PROJ01 CISC3000 UM Database

This is a team project, up to four people, that aims to provide you some experience in database design. We will explore both theoretical and practical aspects of the database design steps. First, you will design an application and then translate the theoretical concepts into the terms of a concrete DBMS to implement the database.
The objective in this project is to adapt the existing University database from our textbook and to turn in the database of the University of Macau.

A. Discuss the changes your group would like to implement in the existing DB to become UM DB. As follows some suggestions:

· Alter attributes to accommodate data from University of Macau. For example, you need to alter the size of the student ID from (5) to (8) or (11) in order to accommodate DB012345 or D-B0-1234-5.
· The group may change primary keys in case your group think it is necessary.
· The group can add any extra constraints or remove any existing one.
· The group may add attributes if you think necessary.

building 建築物

CREATE TABLE building
    (building_id    varchar(4) not null,
     name    varchar(50) not null,
     floors    numeric(2,0) not null,
     primary key (building_id)
    )

Example
building_id - E11 or E21A
name - Faculty of Science and Technology
floors - maximum as 4 (how many floors are there)

classroom

CREATE TABLE classroom
    (building_id    varchar(4) not null,
     room_number    varchar(12) not null,
     capacity    numeric(4,0) not null,
     primary key (building_id, room_number),
     foreign key (building_id) references building 
         on delete cascade
    )

• building -> building_id
  1. varchar(15) -> varchar(4)
• room_number
  1. varchar(7) -> varchar(12)
• foreign key (building_id) references building on delete cascade

faculty

CREATE TABLE faculty
    (faculty_code    varchar(4) not null,
     sid_prefix    char(1) not null,
     name    varchar(50) not null,
     building_id    varchar(4),
     primary key (faculty_code),
     foreign key (building_id) references building
         on delete set null
    )

department

CREATE TABLE department
    (dept_id    varchar(10) not null,
     dept_name    varchar(50) not null,
     building_id    varchar(4),
     budget    numeric(12,2)
         check (budget >= 0) not null,
     parent_dept_id    varchar(10),
     primary key (dept_id),
     foreign key (building_id) references building
         on delete set null,
     foreign key (parent_dept_id) references department
    )

• dept_id
  1. primary key
• dept_name
  1. varchar(20) -> varchar(50)
  2. remove primary key
• building -> building_id
  1. varchar(15) -> varchar(4)
• budget
  1. check (budget > 0) -> check (budget >= 0)
• parent_dept_id
• foreign key (building_id) references building on delete set null
• foreign key (parent_dept_id) references department

course

CREATE TABLE course
    (course_id    char(8) not null,
     course_old_id    char(7),
     title    varchar(50) not null,
     dept_id    varchar(10),
     credits    numeric(2,0)
         check (credits > 0) not null,
     primary key (course_id),
     foreign key (dept_id) references department
         on delete set null
    )

• course_id
  1. varchar(8) -> char(8)
• course_old_id
• dept_name -> dept_id
  1. varchar(20) -> varchar(10)

programme

CREATE TABLE programme
    (programme_id    char(5) not null,
     dept_id    varchar(10),
     credits    numeric(3,0) not null,
     years    numeric(1,0) not null,
     primary key (programme_id),
     foreign key (dept_id) references department
         on delete set null
    )

requires

CREATE TABLE requires
    (programme_id    char(5) not null,
     course_id    char(8) not null,
     type    char(2)
         check (type in ('CM', 'GE', 'RE', 'FE', 'MI', 'DE')) not null,
     primary key (programme_id, course_id),
     foreign key (programme_id) references programme
         on delete cascade,
     foreign key (course_id) references course
         on delete cascade
    )

instructor

CREATE TABLE instructor
    (instructor_id    varchar(6) not null,
     firstname    varchar(40) not null,
     middlename    varchar(40),
     lastname    varchar(40) not null,
     chi_firstname    varchar(3),
     chi_lastname    varchar(2),
     birthdate    date not null,
     address    varchar(500) not null,
     dept_id    varchar(10),
     office    varchar(12) not null,
     salary    numeric(8,2) check (salary > 0) not null,
     primary key (instructor_id),
     foreign key (dept_id) references department
         on delete set null
    )

• ID -> instructor_id
  1. varchar(5) -> varchar(6)
• name -> firstname, middlename, lastname, chi_firstname, chi_lastname
• birthdate
• address
• dept_name -> dept_id
  1. varchar(20) -> varchar(10)
• salary
  1. check (salary > 29000) -> check (salary >= 0)

time_slot

CREATE TABLE time_slot
    (time_slot_id    varchar(4) not null,
     day    char(1)
         check (day in ('M', 'T', 'W', 'H', 'F', 'S', 'U')) not null,
     start_time    time not null,
     end_time    time not null,
     type    varchar(8)
         check (type in ('Lecture', 'Tutorial', 'Lab')) not null,
     primary key (time_slot_id, day, start_time)
    )

• day
  1. varchar(1) -> char(1)
• i_ID -> instructor_id
  1. varchar(5) -> varchar(6)
• start_hr, start_min -> start_time
• end_hr, end_min -> end_time

section

CREATE TABLE section
    (course_id    char(8) not null,
     sec_id    char(3) not null,
     semester    varchar(6)
         check (semester in ('1', '2', 'Summer')) not null,
     year    numeric(4,0)
         check (year > 1701 and year < 2100) not null,
     building_id    varchar(4),
     room_number    varchar(12),
     alt_room_number    varchar(12),
     time_slot_id    varchar(4),
     primary key (course_id, sec_id, semester, year),
     foreign key (course_id) references course
         on delete cascade,
     foreign key (building_id, room_number) references classroom
         on delete set null
    )

• course_id
  1. varchar(8) -> char(8)
• sec_id
  1. varchar(8) -> char(3)
• semester
  1. check (semester in (‘Fall’, ‘Winter’, ‘Spring’, ‘Summer’)) -> check (semester in (‘1’, ‘2’, ‘Summer’))
• building -> building_id
  1. varchar(15) -> varchar(4)
• room_number
  1. varchar(7) -> varchar(12)
• alt_room_number

teaches

CREATE TABLE teaches
    (instructor_id    varchar(6) not null,
     course_id    char(8) not null,
     sec_id    char(3) not null,
     semester    varchar(6) not null,
     year    numeric(4,0) not null,
     primary key (instructor_id, course_id, sec_id, semester, year),
     foreign key (course_id, sec_id, semester, year) references section
         on delete cascade,
     foreign key (instructor_id) references instructor
         on delete cascade
    )

• ID -> instructor_id
  1. varchar(5) -> varchar(6)
• course_id
  1. varchar(8) -> char(8)
• sec_id
  1. varchar(8) -> char(3)

student

CREATE TABLE student
    (student_id    char(7) not null,
     firstname    varchar(40) not null,
     middlename    varchar(40),
     lastname    varchar(40) not null,
     chi_firstname    varchar(3),
     chi_lastname    varchar(2),
     birthdate    date not null,
     address    varchar(500) not null,
     register_date    date not null,
     programme_id    char(5),
     primary key (student_id),
     foreign key (programme_id) references programme
         on delete set null
    )

• ID -> student_id
  1. varchar(5) -> char(7)
• name -> firstname, middlename, lastname, chi_firstname, chi_lastname
• birthdate
• address
• register_date
• programme_id
• tot_cred
  1. removed, calculate by programme, requires, and course table

takes

CREATE TABLE takes
    (student_id    char(7) not null,
     course_id    char(8) not null,
     sec_id    char(3) not null,
     semester    varchar(6) not null,
     year    numeric(4,0) not null,
     grade    varchar(2),
     primary key (student_id, course_id, sec_id, semester, year),
     foreign key (course_id, sec_id, semester, year) references section
         on delete cascade,
     foreign key (student_ID) references student
         on delete cascade
    )

• ID -> student_id
  1. varchar(5) -> char(7)
• course_id
  1. varchar(8) -> char(8)
• sec_id
  1. varchar(8) -> char(3)

advisor

CREATE TABLE advisor
    (student_id    char(7) not null,
     instructor_id    varchar(6) not null,
     primary key (student_id, instructor_id),
     foreign key (instructor_id) references instructor
         on delete cascade,
     foreign key (student_ID) references student
         on delete cascade
    )

• s_ID -> student_id
  1. varchar(5) -> char(7)
• i_ID -> instructor_id
  1. varchar(5) -> varchar(6)
• primary key
  1. (s_ID) -> (student_id, instructor_id)
• foreign key
  1. on delete set null -> on delete set cascade

prereq

CREATE TABLE prereq
    (course_id    char(8) not null,
     prereq_id    char(8) not null,
     primary key (course_id, prereq_id),
     foreign key (course_id) references course
         on delete cascade,
     foreign key (prereq_id) references course
    )

• course_id
  1. varchar(8) -> char(8)
• prereq_id
  1. varchar(8) -> char(8)

B. Specify the assumptions about the database in English (informally).

Here you give the proper explanation of the attributes, keys, the nature of relationships between entities, etc. For those obvious assumptions such as students can take several courses, be brief. However, for those more complex relationships you should explain in a more detailed way. In addition, do not make too many simplifying assumptions.

C. Give an ER diagram,

which will reflect your choice of the entity sets, their relevant attributes, and the relationships among them. Again, make your relationships realistic and meaningful.

D. Convert the ER diagram into a relational database schema.

List all of your SQL statements for creating or altering tables, inserting or updating tables and so on.

E. Samples from your database.

Few tasks to be made here:

· Insert or update the values of the altered attributes.

The number of tuples in the relations should not be different from the existing relations. List all relations and their contents.

Table building

INSERT building (building_id, name, floors) VALUES
('E1', 'University Gallery', 2),
('E11', 'Faculty of Science and Technology', 4),
('E12', 'Faculty of Health Sciences', 4),
('E2', 'UM Wu Yee Sun Library', 6),
('E21A', 'Faculty of Arts and Humanities', 4),
('E21B', 'Faculty of Social Sciences', 4),
('E22', 'Faculty of Business Administration', 4),
('E31', 'Student Activity Centre', 3),
('E32', 'Faculty of Law', 4),
('E33', 'Faculty of Education', 4),
('E34', 'Cultural Building', 4),
('E4', 'Anthony Lau Building', 4),
('E5', 'Central Teaching Building', 4),
('E6', 'Central Teaching Building', 4),
('E7', 'Central Teaching Building', 3)

Table classroom

INSERT classroom (building_id, room_number, capacity) VALUES
('E11', 'E11-1006', 80),
('E11', 'E11-1015', 80),
('E11', 'E11-1028', 80),
('E11', 'E11-2032', 120),
('E11', 'E11-G015', 150),
('E21A', 'E21-G035', 100),
('E21A', 'E21-G036', 100),
('E22', 'E22-1011', 50),
('E22', 'E22-2012', 50),
('E4', 'E6-G078', 300)

Table faculty

INSERT faculty (faculty_code, sid_prefix, name, building_id) VALUES
('FAH', 'A', 'Faculty of Arts and Humanities', 'E21A'),
('FBA', 'B', 'Faculty of Business Administration', 'E22'),
('FED', 'H', 'Faculty of Education', 'E33'),
('FHS', 'C', 'Faculty of Health Sciences', 'E12'),
('FLL', 'L', 'Faculty of Law', 'E32'),
('FSS', 'S', 'Faculty of Social Sciences', 'E21B'),
('FST', 'D', 'Faculty of Science and Technology', 'E11')

Table department

INSERT department (dept_id, dept_name, building_id, budget, faculty_code, parent_dept_id) VALUES
('BEC', 'Department of Business Economics', 'E22', 5000000.00, 'FBA', NULL),
('CEE', 'Department of Civil and Environmental Engineering', 'E11', 3000000.00, 'FST', NULL),
('CIS', 'Department of Computer and Information Science', 'E11', 3000000.00, 'FST', NULL),
('DAIM', 'Department of Accounting and Information Management', 'E22', 3000000.00, 'FBA', NULL),
('FNC', 'Department of Finance', 'E22', 7000000.00, 'FBA', NULL),
('GLS', 'Department of Global Legal Studies', 'E33', 200000000.00, 'FLL', NULL),
('MAT', 'Department of Mathematics', 'E11', 2000000.00, 'FST', NULL),
('PC', 'Department of Physics and Chemistry', 'E11', 3000000.00, 'FST', NULL),
('SAO', 'Student Affairs Office', 'E31', 6000000.00, NULL, NULL),
('SAO-SRS', 'Student Affairs Office - Student Resources Section', 'E31', 2000000.00, NULL, 'SAO')

· Create and list ten reasonable queries (containing ‘join’ or ‘aggregate’).

Explain and execute them, and print the contents for checking.
Submit a report containing all the five sections (A to E) described above.