database design

1. Identify each relationship type in the figure below and write all of the business rules. Using the Oracle Datamodeler tool create a detailed ERD that depicts a relational database model to capture these business rules.

2. Identify each relationship type in the figure below and write all of the business rules. Using the Oracle Datamodeler tool create a detailed ERD that depicts a relational database model to capture these business rules. 

3. Using the example of a medical clinic with patients and tests, provide a representation of how to model this example using the relational model. (write relationships, business rules, ERD)

1. A specialization hierarchy depicts the arrangement of higher-level entity supertypes (parent entities) and lower-level entity subtypes (child entities). Entity supertype contains the common characteristics and the entity subtypes contain the unique characteristics of each entity subtype.

Example: EMPLOYEE supertype and three entity subtypes—PILOT, MECHANIC, and ACCOUNTANT.

· Write the business rules

· Enter sample data to demonstrate inheritance.

2. In a university environment, an employee may be a professor or an administrator.

· Write the business rules

· Draw the ERD showing the inheritance relationship between the super type and sub type entities with appropriate attributes, PKs and foreign key

· Enter sample data to demonstrate inheritance.

3. In a university environment, a student may be a GRAD or UNDERGRAD.

· Write the business rules

· Draw the ERD showing the inheritance relationship between the super type and sub type entities with appropriate attributes, PKs and foreign key

· Enter sample data to demonstrate inheritance.

1. PRACTICE

Course and Class (1:M relationship)

Each course may form 1 or more classes

Each class must have at only one course.

Class and Student (M:N relationship)

Each class must have one or more students

Each student must enroll in one or more classes

Enroll is the bridge entity after the many to many decomposition

To demonstrate your understanding of constraints - PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, plan the constraints that can be applied to each field in the tables below.

TABLE COURSE:

Fields	Constraint type	Description
CRS_CODE	Primary key	Not null, Unique
DEPT_CODE	Not Null, Check	Check CS or MATH or ENG
CRS_DESCRIPTION	Not Null
CRS_CREDIT	Not Null, Check	Check 1, 2,3,4

TABLE CLASS:

Fields	Constraint type	Description
CLASS_CODE	Primary key	Not null, Unique
CRS_CODE	Foreign key	Primary of the parent table Course
CLASS_SECTION	Not Null, Check	Check SEC1,SEC2, SEC3, SEC4, SEC5,SEC6
CLASS_TIME	Not Null, Check	Check between 8 and 10
CLASS_ROOM	Not Null, check	Check 210 or 206 or 205

TABLE ENROLL: (Composite or bridge table)

Fields	Constraint type	Description
CLASS_CODE	Primary key, Foreign key	Composite primary key, class code referring to Class and stu_num referring to Student tables
STU_NUM	Primary key, Foreign key
ENROLL_GRADE	Not Null, Check	Check FM, JR, SM, SR

TABLE STUDENT:

Fields	Constraint type	Description
STU_NUM	Primary key
LNAME	Not Null, CHeck	Check A to Z
FNAME	Not Null, Check	Check A-Z
INIT	Check	Check A_Z
DOB	Not Null	Check year between 1995 and 2000
PHONE	Not Null, Check	Check 301, 202, 240

2. Region and Store (1:M relationship)

Store and Employee (1:N relationship)

Job and Employee (1:N relationship)

To demonstrate your understanding of constraints - PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, plan the constraints that can be applied to each field in the tables below.