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[1]
CREATE TABLE building
(building_id[2] varchar(4)[3] not null,
name[4] varchar(50) not null,
floors[5] numeric(2,0) not null,
primary key (building_id)
)
classroom
CREATE TABLE classroom
(building_id varchar(4) not null,
room_number[6] 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
○ varchar(15) -> varchar(4)
● room_number
○ varchar(7) -> varchar(12)
● foreign key (building_id) references building on delete cascade
faculty
CREATE TABLE faculty
(faculty_code[7] varchar(4) not null,
sid_prefix[8] char(1) not null,
name[9] 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[10] varchar(10) not null,
dept_name[11] varchar(50) not null,
building_id varchar(4),
budget numeric(12,2)
check (budget >= 0) not null,
parent_dept_id[12] 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
○ primary key
● dept_name
○ varchar(20) -> varchar(50)
○ remove primary key
● building -> building_id
○ varchar(15) -> varchar(4)
● budget
○ 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[13] char(8) not null,
course_old_id[14] char(7),
title[15] varchar(50) not null,
dept_id varchar(10),
credits[16] numeric(2,0)
check (credits > 0) not null,
primary key (course_id),
foreign key (dept_id) references department
on delete set null
)
● course_id
○ varchar(8) -> char(8)
● course_old_id
● dept_name -> dept_id
○ varchar(20) -> varchar(10)
programme[17]
CREATE TABLE programme
(programme_id[18] char(5) not null, dept_id varchar(10),
credits[19] numeric(3,0) not null,
years[20] numeric(1,0) not null, primary key (programme_id),
foreign key (dept_id) references department
on delete set null
)
requires[21]
CREATE TABLE requires
(programme_id char(5) not null, course_id char(8) not null,
type[22] 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[23]
CREATE TABLE instructor
(instructor_id[24] varchar(6) not null, firstname[25] varchar(40) not null,
middlename varchar(40),
lastname[26] varchar(40) not null,
chi_firstname varchar(3),
chi_lastname[27] [28] varchar(2),
birthdate date not null,
address[29] varchar(500) not null, dept_id varchar(10),
office[30] 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
○ varchar(5) -> varchar(6)
● name -> firstname, middlename, lastname, chi_firstname, chi_lastname
● birthdate
● address
● dept_name -> dept_id
○ varchar(20) -> varchar(10)
● salary
○ check (salary > 29000) -> check (salary >= 0)
time_slot
CREATE TABLE time_slot
(time_slot_id[31] varchar(4) not null,
day[32] char(1)
check (day in (‘M’, ‘T’, ‘W’, ‘H’, ‘F’, ‘S’, ‘U’)) not null,
start_time[33] time not null,
end_time[34] time not null,
type[35] varchar(8)
check (type in (‘Lecture’, ‘Tutorial’, ‘Lab’)) not null,
primary key (time_slot_id, day, start_time)
)
● day
○ varchar(1) -> char(1)
● i_ID -> instructor_id
○ varchar(5) -> varchar(6)
● start_hr, start_min -> start_time
● end_hr, end_min -> end_time
section
CREATE TABLE section
(course_id[36] char(8) not null,
sec_id[37] char(3) not null,
semester[38] varchar(6)
check (semester in (‘1’, ‘2’, ‘Summer’)) not null,
year[39] numeric(4,0)
check (year > 1701 and year < 2100) not null,
building_id varchar(4),
room_number varchar(12),
alt_room_number[40] varchar(12),
time_slot_id[41] 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
○ varchar(8) -> char(8)
● sec_id
○ varchar(8) -> char(3)
● semester
○ check (semester in (‘Fall’, ‘Winter’, ‘Spring’, ‘Summer’)) -> check (semester in (‘1’, ‘2’, ‘Summer’))
● building -> building_id
○ varchar(15) -> varchar(4)
● room_number
○ 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
○ varchar(5) -> varchar(6)
● course_id
○ varchar(8) -> char(8)
● sec_id
○ varchar(8) -> char(3)
student
CREATE TABLE student
(student_id[42] char(7) not null,
firstname[43] varchar(40) not null,
middlename varchar(40),
lastname[44] varchar(40) not null,
chi_firstname[45] varchar(3),
chi_lastname[46] varchar(2),
birthdate date not null,
address varchar(500) not null,
register_date[47] date not null,
programme_id char(5),
primary key (student_id),
foreign key (programme_id) references programme
on delete set null
)
● ID -> student_id
○ varchar(5) -> char(7)
● name -> firstname, middlename, lastname, chi_firstname, chi_lastname
● birthdate
● address
● register_date
● programme_id
● tot_cred
○ 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
○ varchar(5) -> char(7)
● course_id
○ varchar(8) -> char(8)
● sec_id
○ 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
○ varchar(5) -> char(7)
● i_ID -> instructor_id
○ varchar(5) -> varchar(6)
● primary key
○ (s_ID) -> (student_id, instructor_id)
● foreign key
○ 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
○ varchar(8) -> char(8)
● prereq_id
○ 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.
· 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.
建築物
E11
例如有E21A
Faculty of Science and Technology
4
(有幾多層)
E11-3024
FST
D
(學生證字頭，select學生證number戈陣要攞埋)
Faculty of Science and Technology
CIS
Department of Computer and Information Science
例如好似SAO係一個部門，SAO-SRS係屬下嘅部門，咁dept_id = SAO-SRS, parent_dept_id = SAO
CISC3000
CISB221
Introduction to Database System
3
課程，例如CPS17，ECE17
CPS17
132
(總需要學分)
4
(課程需要年份，例如law嘅可能係5)
一個課程需要修咩科
CM/GE/RE/FE/MI/DE
(戈科嘅類型)
professors
而家係1234-5，落db就用12345，Miguel話澳大諗住加多個位，所以就varchar(6)
Sin Teng
Wong
黃
😳
地址
E11-4032
(office位置)
0001
M = Monday
T = Tuesday
W = Wednesday
H = tHursday
F = Friday
S = Saturday
U = sUnday
17:30
18:45
Lecture
CISC3000
001
2
2019
第二間課室，例如lab會喺第二間課室上
0001
(依到唔會連foreign key，因為佢many to many，time_slot同section個table個primary key太複雜，會太多野寫，但係畫圖會畫埋，要起many to many個table嘅話要run埋個TimeSlotMapping.sql)
B123456
(個D由programme_id link去programme table再link去department table再link去faculty個sid_prefix)
Hou In
Sit
浩然
薛
2017-08-02
(註冊日期)
若有收获，就点个赞吧
0 人点赞
