Waiting for answer This question has not been answered yet. You can hire a professional tutor to get the answer.
CSC 231 Final ProjectFall 2017pg1of8CSC231DatabaseDesign I–FinalProject–Fall 2017General Description and GoalThe goal of thefinalprojectsis todesign a relational database, and crea
CSC 231 Final Project
Fall 2017
pg
1
of
8
CSC
231
D
atabase
Design I
–
Final
Project
–
Fall 2017
General Description and Goal
The goal of the
final
project
s
is to
design a relational database, and create a physical database
in Oracle Application Express.
Th
e steps of the final project
include:
1.
Des
igning
the database and creating an Entity Relationship (E
-
R) diagram
2.
Converting the E
-
R diagram into corresponding table definition diagrams
3.
Writing the SQL
create table
statements to create the physical tables including
constraints
4.
Writing the SQL
insert into
statements to populate the tables
Project Topics
Each student must
choose
one
of the attached
case studies to use for the final project. Each
case study defines the information requirements the database will satisfy.
Requirements
Each final pr
oject must fill the following requirements:
Minimum of
5
entities
Minimum of
4
relationships
Your project will be graded based upon how accurately you model and capture the information
requirements in the case study.
Make sure to carefully read each scen
ario to identify
the
majority of the
information each business requires.
Deadline
The completed projects are due
at the end of the semester, on the day of the final exam period.
(
December
1
8
–
3:30pm
)
All students must email me your project topics by
Mond
ay 11/2
7
.
Presentations
All students are required to make a presentation of his/her final project at the end of the
semester
during the Final Exam period (Monday 12/18 3:30pm)
.
You will present your E
-
R diagram, and describe how your diagram captures the
information
requirements of the case study.
Presentations should be 5
-
10 minutes in length.
CSC 231 Final Project
Fall 2017
pg
2
of
8
Grading Criteria
Criteria
Percentage
E
-
R diagram
Correctly identify entities, attributes and
relationships to capture the important
information requirements
50%
Table Definition
Diagrams
Correctly convert E
-
R diagram into table
definition diagrams
15%
SQL Create Table
Statements
Correctly convert E
-
R diagrams into SQL
create table statement, including PK, FK,
UK, NN and check
constraints
, and
appropriate Oracle d
ata types
20%
SQL Insert Into
Statements
Correctly create 5 SQL insert into
statements for each database table
15%
Total
100%
CSC 231 Final Project
Fall 2017
pg
3
of
8
Amazon.com
Seattle
-
based Amazon.com offers millions of in
-
print and out
-
of
-
print books on its web site. It is also a
to
p online music and video retailer. The company offers perks, such as free e
-
mail notification of new
books, recommendations for specified genres or subjects, and book reviews. Its site also offers free
electronic greeting cards and online auctions for a
variety of products. The owner of the company is
interested in improving their database system.
Every book can be identified either by its unique ISBN number or by the combination of author name
and book title. The majority of the books available on our
website are still in print, but many of the
books we offer online are out
-
of
-
print books. For this reason, we need to keep track of whether a book
is still in print as well as its publishing house information (so that we can order it if it’s not in stock
). We
don’ have a simple way to distinguish the publishing houses but we need to keep track of their full
address.
Once a book goes out of print, we need to indicate that its status has changed. The customer can then
search the out
-
of
-
print books to se
e if it is available. For out
-
of
-
print books we work with a network of
local suppliers. Once again, we don’t have an easy way to identify the suppliers and we need to keep
their full mailing address.
We also use keywords to identify the books on our web
site. They describe the type of book and can be
used by customers to look up books they may have an interest in. Some of our keywords are: mystery,
fiction, fables, detectives, sports, history, America, Europe, language, and computers. We make a listin
g
of every book that has been associated with a keyword (by ISBN number). This way listings of books by
keywords can be produced for customers who are looking for books, but don’t have a specific author or
title in mind.
As for our customers, we allow th
e people who surf our website to search for books and listings of books
by title or keyword or author or ISBN number.
The search history of our customers is extremely valuable
to the company. We use the search history to identify which keywords, books, aut
hors, etc. are most
frequently searched for on our system. This information is reviewed by our buyers weekly.
CSC 231 Final Project
Fall 2017
pg
4
of
8
San Juan Sailboat Charters
San Juan Sailboat Charters (SJSBC) is an agency that leases (charters) sailboats. SJSBC does not own the
boats. Inste
ad, SJSBL leases boats on behalf of boat owners who want to earn income from their boats
when they are not using the boats themselves, and SJSBC charges the owners a fee for this services.
SJSBC specializes in boats that can be used for multiday or weekly
charters. The smallest sailboat
available is 28 feet in length, and the largest is 541 feet in length.
Each sailboat is fully equipped at the time it is leased. The majority of the equipment is provided at the
time of the charter. The majority of the equi
pment is provided by the owners, but some is provided by
SJSBC. Some of the owner
-
provided equipment is attached to the boat, such as radios, compasses,
depth indicators and other instrumentation, stoves, and refrigerators. Other owner
-
provided equipment
i
s not physically attached to the board, such as sails, lines, anchors, dinghies, life preservers, and
equipment in the cabin (dishes, silverware, cooking utensils, bedding and so
-
on.) SJSBC provides
consumable supplies such as charts, navigation books, tid
e and current tables, soap, dish towels, toilet
paper, and similar items. The consumable supplies are treated as equipment by SJSBC for tracking and
accounting purposes.
Keeping track of equipment is an important part of SJSBC’s responsibilities. Much of
the equipment is
expensive, and those items not physically attached to the boat can be easily damaged, lost, or stolen.
SJSBC holds the customer responsible for all of the boat’s equipment during the period of their charter.
SJSBC likes to keep accurate r
ecords of its customers and charters, and customers are required to keep a
log during each charter. Some itineraries and weather conditions are more dangerous than others, and
the data from these logs provides information about the customer experience. Thi
s information is useful
for marketing purposes, as well as for evaluating a customer’s ability to handle a particular boat and
itinerary.
CSC 231 Final Project
Fall 2017
pg
5
of
8
Music Store
s
You are the owner of a
chain of
music store
s
. You need to set up a system to automate all of the dai
ly
business activity for
each
store. You currently only have
three
store location
s
,
and are hoping to open a
new location next year
. Your music store
s
do a fair amount of sales each day, usually about $1500
worth.
Each store has a store manager and anyw
here from 2
-
5 additional full
-
time and part
-
time
employees.
Each store
sell CD's, but you also sell sheet music, concert tickets, and your own secret item. For each
sale, you would like to record a unique transaction number and the date of the sale. Yo
u would also like
to know which employee made the sale, each item being sold, the quantity of that item, the amount of
each item, the form of payment, and the total amount for the purchase
. Each quarter you compare the
sales at each store an
d
provide small
bonuses to the manager at the high
-
performing store to distribute
to the store employees.
Each quarter you also provide a special discount to your frequent customers
who have made the most purchases over the quarter. These special discounts are emailed to
the
customers from the store managers.
For all of the items in each
store, you need to keep a very detailed inventory. You need to know the
type and name of the item, the artist or group, the price of that item, and amount of that item currently
in stoc
k. If the item is not in stock, you will have to keep the expected re
-
stock date. If the item is in
stock, you will need to know how many are left so that you know when to re
-
order. For concert tickets,
you keep special information. You keep the name o
f the group, the date and time of the concert,
location, row and seat number, and price. You would also like to be able to keep a listing of the
different type of items that you keep in our store, kind of like a category list, such as CD, sheet music,
you
r secret item, etc. In addition, you would like to keep a listing of all artists that you have CDs and
tickets for, the composer of sheet music, and any specific information that you need for your secret
item. If a customer is looking for an item by a pa
rticular artist or group, you can tell them what you have
to offer.
Each store manager can view the inventory at the other stores. This is helpful so if one store is out of an
item that another store has, the customer can be directed to go to the other st
ore to purchase the item
they are looking for.
CSC 231 Final Project
Fall 2017
pg
6
of
8
Nike Corporation
Nike currently employs well over 22,000 people in a variety of jobs ranging from CEO to fashion designer
to accountants. We need to keep basic contact information for every employee as well
as the date they
were hired by our company and the date they leave our company, as well as the department the person
is assigned to. Some of our employees actually design or make the products that we sell. Others are
simply management. Everyone is assi
gned to a department, no exceptions.
We have departments in many locations around the world including Beaverton, Oregon and Hilversum,
The Netherlands. It is possible to have a department exist in more than one location (for example,
receiving, shipping
and payroll are all departments at each location). We want to keep information on
each location and the number and types of buildings we have there (including sq. ft. for office space).
Additionally, we contact with suppliers, retailers, and delivery per
sonnel all around the world to make
our business successful. We need to know who supplies which item, how much the item costs, how to
contact our supplier and what method they use to deliver the item to us. For our retailers we need
basic contact informa
tion as well as what type of store they are (department or specialty), the
neighborhood the store is in (urban, rural, upscale), and if the store is part of a chain or not. For the
delivery methods we need to know basic contact information for the company
as well as who provides a
discount for bulk shipping and what the discount percentage is.
Here at Nike we have a number of products that we sell including apparel and shoes. We have apparel
for men, women and children. For each item we need to know the
price we sell it to our retailers, if it is
for men, women, or children, the sport it is for, where it is made, how many we have sold in the past
and where it sells the most. We need to keep track of which products go to which stores and how they
get the
re.
CSC 231 Final Project
Fall 2017
pg
7
of
8
Garden Glory
Landscaping
Garden Glory is a partnership that provides gardening and yard maintenance services to customers.
Customers can either be individual home owners or organizations. Garden Glory employs both full and
part
-
time gardeners.
Some o
f their gardeners have worked with them for over 10 years. Keeping track of
how long the gardeners have worked is important because it impacts the pay the gardeners receive.
Garden glory will provide one
-
time services, but it specializes in ongoing mainte
nan
ce.
Generally, each
service has an approximate rate that the customer is charged.
Many of their
customers have multiple
buildings, apartments, and rental houses that requires gardening and lawn maintenance services.
Garden Glory wants their customers to
view them as the “One
-
Stop Shop” for all their gardening and
lawn
-
service needs. Garden Glory emails their customers coupons and special deals based upon their
existing services and how long they hav
e been a Garden Glory customer.
Garden Glory needs to s
tore information on each property they maintain.
In addition to just the address
information, Garden Glory needs to ensure that they have sufficient data on each property, such as the
approximate size, and any challenges in accessing or working on the prop
erty.
Garden Glory also needs to track their equipment
(lawnmowers, leaf blowers, tractors, chainsaws, etc.)
,
and how/when their equipment is
repair
ed
. Keeping an inventory of equipment is critical for Garden
Glory to ensure equipment does not go missing
or fall in disrepair. Furthermore, all the gardeners need
to be trained before they can use certain equipment, and management wants to be able to determine
who has obtained training on which equipment.
Additionally, there are three training levels (beginn
ing,
intermediate, advanced).
For each of our gardeners we need to know the current training level for each
equipment that requires training.
CSC 231 Final Project
Fall 2017
pg
8
of
8
Walt Disney World
The brainchild of Walter Elias Disney, a resort that bears his name, is his legacy to the wor
ld. Along with
a cast of memorable cartoon characters led by a mouse named Mickey, Disney opened the doors of the
Magic Kingdom to the public in an effort to provide a place where childhood dreams reign supreme.
Before this death, he had already planned
the future expansion of the Disney realm with his design for
Epcot. Today, his Florida namesake, with its four parks, is one of the most visited tourist sites in the
world.
Each park is divided into areas, each with its own theme. In the Magic Kingdom,
all the attractions and
shops in Frontierland have a western theme while the ones in Tomorrowland have a futuristic/space
them. In Epcot Center, the attractions in each of the World Showcase countries all share the
atmosphere, architecture, music and food
of the specific country and the attractions in Future World
have themes like: energy; transportation; and the horticulture. In the Disney MGM
-
Studios, there are
several areas (like New York Street and Hollywood Boulevard), each with stores and attractio
ns that fit
in with the atmosphere and theatrical significance of the area. Finally, in the newest park, Animal
Kingdom, guests are able to experience attractions like Dinosaur in the Dinoland USA area and the Tree
of Life in the Safari Village.
Each par
k has a unique landmark with identifies it: Cinderella’s Castle in the Magic Kingdom, Spaceship
Earth in Epcot, The Earful Tower in the Disney MGM Studios and the Tree of Life in Animal Kingdom.
Attractions are subdivided into shops, shows, and rides. A
ll are identified by name and we keep track of
the maximum capacity for each, the minimum number of employees needed for operation. For rides,
we keep track of minimum height required, operational status, date and time of last maintenance,
transport mode
(carts, conveyor belts, magnetic track, motorized track, etc.) wait time and duration of
ride. For shops we need to know the type of store (restaurant, fast food, clothing, jewelry, hats, etc.).
For shows we need to know the number of shows per day and t
he duration of each show. We also need
to know if the show is subject to weather conditions.
In order to enter any of our parks, tickets must be purchased. Every person over the age of three that
enters any of our parks must have a ticket. Tickets are
either one park/one day or multiple day tickets.
Every ticket is assigned a specific ticket number. We don’t keep track of any information about our
customers and tickets are not specifically assigned to a person...they are either child tickets or adult
ti
ckets. For our purposes, a child is anyone under the age of 12. Tickets don’t expire but we do need to
keep track of the current date of use and the number of days remaining on the ticket as well as whether
the status of the ticket is valid or used. For
one day/one park tickets we need to also track which park
the ticket was used at so that it will only be valid at that park for the remainder of the day.
