DBMS: Exercises based on Relational Model – Part 2
Topics discussed:
A solved problem and a homework problem based on Relational Model concepts:
1. Consider the following relations for a database that keeps track of student enrollment in courses and books adopted for each course:
STUDENT (SSN, Name, Major, Bdate)
COURSE (Course#, Quarter, Grade)
ENROLL (SSN, Course#, Quarter, Grade)
BOOK_ADOPTION(Course#, Quarter, Book_ISBN)
TEXT (Book_ISBN, Book_Title, Publisher, Author)
Specify the foreign keys for this schema, stating any assumptions you make.
2. Suppose that each of the following update operations is applied directly to the database state shown in the figure. Discuss all integrity constraints violated by each operation, if any, and the different ways of enforcing these constraints.
(i) Insert (‘665985475’, ‘Andrew’, 32000, 3) into EMPLOYEE.
(ii) Insert (‘185985475’, ‘Jessica’, 40000, 7) into EMPLOYEE.
(iii) Insert (null, ‘Computerization’, 3) into PROJECT.
(iv) Insert (1, ‘Administration’, ‘123456879’) into DEPARTMENT.
(v) Insert (null, ‘Finance’, ‘969598211’) into DEPARTMENT.
(vi) Delete the EMPLOYEE tuple with SSN = ‘452635142’.
(vii) Delete the WORKS_ON tuple with SSN = ‘452635142’ and PNo = 3.
(viii) Delete the PROJECT tuple with PNo = 10.
(ix) Modify the DNo of the EMPLOYEE tuple with Mgr_SSN = ‘452635142’ to 7.
(x) Modify the Hours of the WORKS_ON tuple with SSN = ‘452635142’ and PNo = 1 to 20.

Follow Neso Academy on Instagram: @nesoacademy(https://bit.ly/2XP63OE)

Contribute: http://www.nesoacademy.org/donate

Memberships: https://bit.ly/2U7YSPI

Books: http://www.nesoacademy.org/recommended-books

Website ► http://www.nesoacademy.org/
Forum ► http://forum.nesoacademy.org/
Facebook ► https://goo.gl/Nt0PmB
Twitter ► https://twitter.com/nesoacademy

Music:
Axol x Alex Skrindo - You [NCS Release]

#DBMSByNeso #DBMS #RelationalDataModel