Question: #1255

Assignment 1: Relational Model and Integrity Constraints

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

 

 

 

 

 

 

 

 

  1. 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

 

 

 

 

Waiting for Answers!

No solution has been posted yet. You have opportunity make $20!

Related Solutions

No related questions were found.

Comments

Online Users