After you have reviewed the E-R model, create a relational database design for t
ID: 3860578 • Letter: A
Question
After you have reviewed the E-R model, create a relational database design for the model.
1. For each entity in the E-R model, specify a table structure, determine the data types and additional column properties, identify primary keys and foreign keys, and verify normalization on the resulting tables. Refer to the metadata table of Figure 5-26 on page 312, Chapter 5 in the textbook as a guide for format to document detailed column properties.
Column Name
Data Type
Key
Required
Default Value
Remarks
2. Document referential integrity constraint enforcement actions and any business rules that you think are important. Use the provided RI table template below to fill in as appropriate (similar to part of your team project D3 requirements). You can also refer to Figure 5-29 on page 317, Chapter 5 in the textbook for an example.
Note: Use the following referential integrity table template for question 2 of the assignment.
Relationship
Referential Integrity Constraint
Cascading Behavior
PARENT
CHILD
ON UPDATE
ON DELETE
Column Name
Data Type
Key
Required
Default Value
Remarks
DEPARTMENT PROFESSOR COLLEGE PK DepartmentName Chairs----H1PK PK ProfFirstName PK ProfLastName PK CollegeName Phone TotalMajors Building Room has DeanFirstName LH DeanLastName Phone Building Room Building OfficeNumber Phone contains Major STUDENT PK StudentNumber Title StudFirstName StudLastName HomeStreet HomeCity HomeState HomeZip Phone AdvisesExplanation / Answer
If you have any doubts, please give me comment...
CREATE TABLE COLLEGE(
CollegeName VARCHAR(100) NOT NULL PRIMARY KEY,
DeanFirstName VARCHAR(100),
DeanLastName VARCHAR(100),
Phone CHAR(15),
Bilding VARCHAR(30),
Room VARCHAR(50)
);
CREATE TABLE DEPARTMENT(
DepartmentName VARCHAR(100) NOT NULL PRIMARY KEY,
Phone CHAR(15),
TotalMajors INTEGER,
Building VARCHAR(30),
Room VARCHAR(50),
CollegeName VARCHAR(100),
FOREIGN KEY(CollegeName) REFERENCES COLLEGE(CollegeName) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE PROFESSOR(
ProfFirstName VARCHAR(50),
ProfLastName VARCHAR(50),
Building VARCHAR(30),
OfficeNumber INTEGER,
Phone CHAR(15),
DepartmentName VARCHAR(100),
PRIMARY KEY(ProfFirstName, ProfLastName),
FOREIGN KEY(DepartmentName) REFERENCES DEPARTMENT(DepartmentName) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE STUDENT(
StudentNumber INTEGER PRIMARY KEY,
Title VARCHAR(5),
StudFirstName VARCHAR(50),
StudLastName VARCHAR(50),
HomeStreet VARCHAR(50),
HomeCity VARCHAR(30),
HomeState CHAR(2),
HomeZip CHAR(5),
Phone CHAR(15),
ProfFirstName VARCHAR(50),
ProfLastName VARCHAR(50),
FOREIGN KEY(ProfFirstName, ProfLastName) REFERENCES PROFESSOR(ProfFirstName, ProfLastName)
);
CREATE TABLE STUDENT(
StudentNumber INTEGER,
DepartmentName VARCHAR(100),
FOREIGN KEY(DepartmentName) REFERENCES DEPARTMENT(DepartmentName),
FOREIGN KEY(StudentNumber) REFERENCES STUDENT(StudentNumber)
);