Recall that in Data Modeling section ( Module 6, Lecture Notes of Class19 .pdf )
ID: 3669735 • Letter: R
Question
Recall that in Data Modeling section (Module 6, Lecture Notes of Class19.pdf) we have developed the E-R model for the Highline University database. See below for a copy of the final data model (created in MS Visio) we developed in class. After you have reviewed the E-R model, create a relational database design for the model.
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 tableof Figure 5-26 on page 312, Chapter 5 in the textbook as a guide for format to document detailed column properties.
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.
Explanation / Answer
create table college
(
collegeName varchar2(200) primary key,
deanfirstName varchar2(50),
deanLastName varchar2(50),
phone number(10),
building varchar2(100),
room number(4)
);
department
(
departmentName varchar2(100) primary key,
phone number(10),
totalMajors number(2),
building varchar2(100),
room number(4)
);
professor
(
proffirstName varchar2(50),
profLastName varchar2(50),
officeNumber number(10),
building varchar2(100),
room number(4),
constraint prof_pk primary key (proffirstName,profLastName)
);
student
(
studentNumber varchar2(10) primary key,
title varchar2(5),
studfirstName varchar2(50),
studLastName varchar2(50),
homestreet varchar2(100),
homecity varchar2(100),
homestate varchar2(100),
homezip number(10),
phone number(10)
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.