Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
CS 122A: Introduction to Data Management
Homework 2: E/R and Relational Translation (100 Points)
Submission
All homework assignments should have the student IDs and names of your team members. Remember that all homework assignments should be done in a group. This homework assignment should be submitted on EEE before 11:45 pm on the due date. Only one student in a group should submit the file. Everybody on the team isrequired to have the finally submitted version. Refer to the following table for the submission guidelines. After the 24hour grace period, no more submission is allowed on EEE. That is, we will not accept assignments after that time. We will publish the solutions at that time for the next assignment. Please get all your work in on time!
ER Schema Design [100 pts]
You have successfully designed an ER diagram for “UC Airlines (UCA)”. Now, to setup a real database, you are required to design a relational schema to represent the objects described in the ER diagram that you created. Specifically, you need to create a SQL DDL statement for each table. Since you sent your ER diagram to an external databaseconsulting firm, “DBInstructor”, to check the correctness of your design, “DBInstructor” will provide the final ER diagram soon. Start designing a relational schema based on your original ER diagram. However, your final design should be based on the solution that “DBInstructor” provides. It will be released shortly.
If you see any opportunities to avoid creating excess relations that stem from relationships, avoid them so as to make the relations simpler. Clearly list all of the relations and their attributes (including their types), primary keys and foreign keys (including referenced target relations) by creating a SQL DDL statement for each table. As a result, your design shouldcapture the information and constraints of the ER diagram as faithfully as possible. For types, use one of the following types for each column.
|
Category |
Type |
Remark |
|
NUMBER |
INTEGER |
A number type for integer values |
|
DECIMAL(x,y) |
A number type for real values where x is the maximum number of digits and y is the number of digits to the right of the decimal point. |
|
|
STRING |
CHAR(n) |
A fixedlength string type where n represents the column length. |
|
VARCHAR(n) |
A variablelength string type where n represents the maximum column length. |
|
|
DATETIME |
DATE |
A type that is used for values with a date part but no time part. The format is '00000000'. |
|
TIME |
A type that is used for values with time part. The format is '00:00:00'. |
|
|
DATETIME |
A type that is used for values that contain both date and time parts. The format is '00000000 00:00:00'. |
Wherever possible, use the entity, relationship, and attribute namesfrom the final ER diagram for naming your tables and columns (to make it clear how your design correspondsto their ER diagram). Again, all of the following should be included in a DDL statement for each table. Be sure to:
(a) [50pts] List the tables and columns in your design.
(b) [25pts] For each table, identify its primary key column(s).
(c) [25pts] For each table, identify its foreign key column(s) and other table(s) that are referenced.
Here is an example DDL statement for a table:
CREATE TABLE Professor(
eid INTEGER,
name VARCHAR(40),
department VARCHAR(40),
phd_year INTEGER,
phd_school VARCHAR(80),
PRIMARY KEY (eid)
);