Reference no: EM13926375
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
|
|
Explain binomial distribution
: There are 40 multiple choice questions in a history test. For each question, there are 5 alterna- tives of which only one alternative is the correct answer.
|
|
The primary purpose of the statement of cash flows
: Disney's variable costs are 30% of sales. The company is contemplating an advertising campaign that will cost $22,000. If sales are expected to increase $40,000, by how much will the company's net income increase?
|
|
Fostering child development through empowerment
: Step 2: Review the following articles: "The Foundations of Lifelong Health Are Built in Early Childhood" and "Fostering Child Development through Empowerment, Unity, and Cooperation."
|
|
What would you like to focus on during your visit today
: What would you like to focus on during your visit today?Would a family assessment tool be appropriate? If not, why not, if so, which one and why?
|
|
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.
|
|
How many minutes could the driver expect to be at station
: If a truck was just arriving at the station, about how many minutes could the driver expect to be at the station? What is the probability that both inspectors would be busy at the same time?
|
|
Correlation between alcoholism and parental abuse
: Write about Correlation between Alcoholism and Parental Abuse
|
|
Is this data a good t for the one-way normal model
: A consumer testing agency tested the lifetimes of ve brands of dot-matrix computer printer ribbons using a single printer.
|
|
Specific identification method for inventory costing
: Of the following companies, which one would not likely employ the specific identification method for inventory costing?
|