Database Management Systems - Exercises based on Relational Model (Part 2)

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://bit.ly/3EpZgBD Memberships: https://bit.ly/2U7YSPI Discord: https://bit.ly/3HiGtJr WhatsApp: https://whatsapp.com/channel/0029Va9B1Bq4tRru0nqgtx3h Books: https://bit.ly/4cZYQil Website ► https://www.nesoacademy.org/ App ► https://play.google.com/store/apps/details?id=org.nesoacademy Facebook ► https://www.facebook.com/nesoacademy Twitter [X] ► https://x.com/nesoacademy Music: Axol x Alex Skrindo - You [NCS Release] #DBMSByNeso #DBMS #RelationalDataModel