ER model exercise cont. #

Add this assignment into the weekly hw upload.

1. The university keeps track of each student’s name (first name, middle name, last name), student number, social security number, permanent address and phone, birthdate, sex, class (e.g. freshman, sophomore, …, graduate), and degree program (e.g. B.A., B.S., …, Ph.D.). Some user applications need to refer to the street, city, state, and zip of the student’s permanent address, and to the student’s last name. Both social security number and student number have unique values for each student.

   

2. Each department is described by a name, department code, office number, office phone, and college. Both name and code have unique values for each department.

   

3. The university keeps track of each student’s minor department (if any) and major department. A student majors in exactly one department. One department can have many major students (at least one) and many minor students (at least one). A student can be a minor of at most one department.

   

4. Each course has a course name, description, course number, number of semester hours, level, and offering department. The value of course number is unique for each course.

   Some departments may not offer courses. Some department may offer many courses. A course must be offered by one and only one department.

   

5. Each section of a course has an instructor, semester, year, and section number. The section number distinguishes different sections of the same course that are taught during the same semester/year.

   A course has at least one section and can have many sections.

   

Relational data model and relational database constraints #

File: Ch 3 slides

For example,

• for an attribute like degree_program belonging to student, the domain may be {BA, BS, BE, PhD, MD}.
• for an attribute like department_number belonging to department, the domain may be {1, 2, 3, …, 20}.

Schema #

These schemas don’t show anything about the actual instances, it is just the abstract structure.

State #

All relations have

• schema
• state

An example state may look like:

t1 = <'Benjamin Bayer', '305-61-2435', ..., 3.21>

Values may be null if value is unknown or not applicable.

Tuples themselves are unordered, so changing their order is still considered the same state. Updating the values are considered a new state.