Database Systems
Assignment 1: Relational Model and Integrity Constraints
Answer all questions with reference to the STUDENT, COURSE, and GRADE tables presented below for which the primary keys, foreign keys, and referential integrity constraints are specified.
STUDENT
Primary Key: STUDENT_ID
|
STUDENT_ID
|
STUDENT_LNAME
|
STUDENT_FNAME
|
MAJOR
|
|
150-70-5879
|
Jones
|
Ray
|
CS
|
|
276-32-4380
|
Brown
|
John
|
MA
|
|
280-90-8766
|
Lee
|
Mae
|
CS
|
|
327-50-9090
|
Green
|
Bruce
|
CS
|
|
370-80-5050
|
Cooper
|
Bill
|
MA
|
|
430-76-6858
|
Woo
|
Dan
|
EE
|
|
430-80-9262
|
Bose
|
Sameer
|
EE
|
|
526-77-3568
|
Ryan
|
Pat
|
CS
|
|
527-90-5470
|
McGraw
|
Julia
|
CS
|
|
660-80-5497
|
Gonzales
|
Jose
|
CS
|
|
720-60-5000
|
Parker
|
Nancy
|
CS
|
|
824-58-5473
|
Smith
|
John
|
MA
|
|
900-90-9000
|
Bush
|
Bill
|
EE
|
|
925-05-6872
|
Liu
|
Tsui
|
EE
|
|
967-54-7112
|
Wang
|
John
|
MA
|
COURSE
Primary Key: COURSE_NO
|
COURSE_NO
|
COURSE_NAME
|
DEPT_CODE
|
CREDITS
|
|
CS100
|
Data Structures
|
CS
|
3
|
|
CS120
|
Algorithms
|
CS
|
3
|
|
CS200
|
Object Oriented Programming
|
CS
|
4
|
|
CS220
|
Database Management
|
CS
|
4
|
|
CS240
|
Data Communications
|
CS
|
4
|
|
CS300
|
Distributed Systems
|
CS
|
4
|
|
EE100
|
Circuit Analysis
|
EE
|
3
|
|
EE120
|
Communication Theory
|
EE
|
3
|
|
EE200
|
VLSI Design
|
EE
|
4
|
|
EE300
|
Packet Switching Networks
|
EE
|
4
|
|
MA100
|
Calculus
|
MA
|
3
|
|
MA200
|
Advanced Calculus
|
MA
|
4
|
|
MA220
|
Discrete Mathematics
|
MA
|
4
|
|
MA300
|
Number Theory
|
MA
|
4
|
GRADE
Primary Key: COURSE_NO + STUDENT_ID
Foreign Keys: COURSE_NO references COURSE , RESTRICT on DELETE
STUDENT_ID references STUDENT , CASCADE on DELETE
|
COURSE_NO
|
STUDENT_ID
|
GRADE
|
|
CS100
|
150-70-5879
|
B
|
|
EE100
|
150-70-5879
|
B
|
|
EE100
|
276-32-4380
|
C
|
|
CS100
|
280-90-8766
|
A
|
|
EE100
|
280-90-8766
|
A
|
|
CS100
|
327-50-9090
|
B
|
|
CS100
|
430-76-6858
|
A
|
|
EE100
|
430-76-6858
|
B
|
|
MA220
|
660-80-5497
|
B
|
|
CS200
|
720-60-5000
|
B
|
|
CS200
|
824-58-5473
|
C
|
|
MA220
|
824-58-5473
|
B
|
|
EE200
|
824-58-5473
|
B
|
|
MA220
|
900-90-9000
|
B
|
|
CS200
|
925-05-6872
|
A
|
|
EE200
|
925-05-6872
|
A
|
|
CS200
|
967-54-7112
|
A
|
|
EE200
|
967-54-7112
|
B
|
1. Explain what happens when you try to delete the following records from the COURSE table:
(a)
|
COURSE_NO
|
COURSE_NAME
|
DEPT_CODE
|
CREDITS
|
|
CS120
|
Algorithms
|
CS
|
3
|
(b)
|
COURSE_NO
|
COURSE_NAME
|
DEPT_CODE
|
CREDITS
|
|
CS100
|
Data Structures
|
CS
|
3
|
2. Specify whether the following records can be added to the GRADE table.
Justify your answer in each case:
(a)
|
COURSE_NO
|
STUDENT_ID
|
GRADE
|
|
CS220
|
900-90-9000
|
B
|
(b)
|
COURSE_NO
|
STUDENT_ID
|
GRADE
|
|
CS100
|
|
B
|
(c)
|
COURSE_NO
|
STUDENT_ID
|
GRADE
|
|
CS100
|
980-70-5879
|
B
|
(d)
|
COURSE_NO
|
STUDENT_ID
|
GRADE
|
|
MA220
|
967-54-7112
|
B
|
2. Explain what happens when you try to delete the following records from the STUDENT table:
(a)
|
STUDENT_ID
|
STUDENT_LNAME
|
STUDENT_FNAME
|
MAJOR
|
|
150-70-5879
|
Jones
|
Ray
|
CS
|
(b)
|
STUDENT_ID
|
STUDENT_LNAME
|
STUDENT_FNAME
|
MAJOR
|
|
280-90-8766
|
Lee
|
Mae
|
CS
|