Please help me with this. Taking an SQL relational data bases course with Oracle, and having trouble. Any help is greatly appreicated! You will upvote clear helpful answer, Thanks!
1. Consider a relational database schema design for a take-out restaurant (as a mini world)
The basic user requirements are the following. You can add more assumptions or requirements to either complicate or simplify the problem.
Menu should list all dishes available for customers to order. Each dish should be listed with a unique name and one price.
Order should be created whenever a customer made an order.
Payment information should be maintained whenever the money is paid. The payment should show to show the order id, cashier id, amount, date etc.
You can use your imagination to figure out what kinds attributes are useful for each essential entity set and figure out the relationship between or among entity sets.
To figure out what attributes you should capture for the database, think about how to answer questions like the ones listed here ( you can have more reasonable questions to help you figure out what need to be captured.)
- Who (in terms of order iD, since you don't track names) bought a meat lover pizza on Jan. 23, 2006?
- Who is the cashier to collect the money for order 001?
- What dishes has a customer (in terms of order iD) ordered for order 10001?
- How much money has been paid for order 10002?
- What are the dishes for order 001?
Steps to go through
E-R diagram (how to draw ER diagram, ) Submit a print out.
Table designs or relation schema = relation name + attributes, in order (+ types of attributes). Example: Instructor(name: string, ID: number). ID is underscored because it is supposed to be the primary key for the table. Submit a print out (see slides 16-1 for an example)
ORACLE SQL command to create those tables. submit scripts
For each table, you should provide at least 4 sample records could be more to illustrate the idea (tuples). submit scripts
2. point out any problem that the following database design has? (10)
|
Ssn
|
Name
|
Coursed
|
Course name
|
instructor
|
|
8766
|
John
|
109
|
C language
|
James
|
|
8766
|
John
|
242
|
Database
|
Stephen
|
|
1334
|
Bill
|
242
|
Database
|
Stephen
|
Hint 1:
Show
why there are data redundancy?
why there are delete anomaly?
Why there are update anomaly?
Why there are insert anomaly?
Hint 2: See notes of recent lecture. lecture 17 (data anomalies)
3. Can you figure out a better design for the information stored in the above table using ERD approach? Justify your design.
(It is possible to figure out a good database design without even going through ERD, because this problem is simple enough; however, for a large scale database, analysis at higher conceptual level such as ER analysis is usually a must. Therefore, a direct result without going through ERD and mapping is not a complete solution!)