Database Systems Assignment 1

   

  • Submission Guideline: Upload your homework file (hw1_[your student id].doc or hw1_[your student id].hwp) to online Uclass classroom. You are allowed to write down your answers either in Korean or English at your convenience.
  • Deadline : 14:00 PM, Friday, October 6 18:00 PM, Wednesday, October 11, 2023
  • 한글 버전

       

Please feel free to leave your questions about homework problems in the online Ed Discussion platform. If you have any questions or suggestions that you want to ask privately, please send an email to me or TA.

   

TA: 이상민(sangmin.lee.ai@gmail.com)

   


  1. (1 mark) What is the difference between controlled and uncontrolled redundancy? Illustrate with examples.

   


  1. (1 mark) Give some examples of integrity constaints that you think should hold on the database shown in the Figure below (Figure 1.2 from our textbook):

Figure 1.2

   


  1. (3 marks) Consider the following set of requirements for a university database that is used to keep track of students’ transcripts. This is similar but not identical to the database shown in Figure 1.2 (you can find it in this page!) of the textbook:

    (a) The university keeps track of each student’s name, student number, social security number, current address and phone, permanent address and phone, birthdate, sex, class (freshman, sophomore, …, graduate), major department, minor department (if any), and degree program (B.A., B.S., …, Ph.D.). Some user applications need to refer to the city, state, and zip of the student’s permanent address, and to the student’s last name. Both social security number and student number have unique values for each student.

    (b) Each department is described by a name, department code, office number, office phone, and college. Both name and code have unique values for each department.

    (c) Each course has a course name, description, course number, number of semester hours, level, and offering department. The value of course number is unique for each course.

    (d) Each section has an instructor, semester, year, course, and section number. The section number distinguishes different sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, …; up to the number of sections taught during each semester.

    (e) A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3, 4 for F, D, C, B, A, respectively).

    Design an ER schema for this application, and draw an ER diagram for that schema using the DB modeling tool `ERDCloud’. Specify key attributes of each entity type and structural constraints on each relationship type. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.

    We strongly suggest you to go through the guideline made by our TA (Mr. Sangmin Lee). You can also use any other DB modeling tool if you want. If you use other tools, please submit the ER diagram in PDF format.

   


  1. (1 mark) Consider the ER diagram given below (Figure 3.21 from our textbook), which shows a simplified schema for an airline reservations system. Extract from the ER diagram the requirements and constraints that resulted in this schema. Try to be as precise as possible in your requirements and constraints specification.

    Hint: Please refer to Problem 3 on how to write down the requirements and constraints.

Figure 3.21

   


  1. (1 mark) Consider the ER diagram given below (Figure 3.22 from our textbook) for part of a BANK database. Each bank can have multiple branches, and each branch can have multiple accounts and loans.

    (a) List the strong (nonweak) entity types in the ER diagram.

    (b) Is there a weak entity type? If so, give its name, partial key, and identifying relationship.

    (c) What constraints do the partial key and the identifying relationship of the weak entity type specify in this diagram?

    (d) List the names of all relationship types, and specify the (min, max) constraint on each participation of an entity type in a relationship type. Justify your choices.

Figure 3.22

   


  1. (1 mark) Suppose that each of the following Update operations is applied directly to the database state shown in the figure below (Figure 5.6 from the textbook). Discuss all integrity constraints violated by each operation, if any, and the different ways of enforcing these constraints.

    (a) Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle Rd, Bellaire, TX’, M, 58000, ‘888665555’, 1> into EMPLOYEE.

    (b) Insert <‘ProductA’, 4, ‘Bellaire’, 2> into PROJECT.

    (c) Insert <‘Production’, 4, ‘943775543’, ‘2007-10-01’> into DEPARTMENT.

    (d) Insert <‘677678989’, NULL, ‘40.0’> into WORKS_ON.

    (e) Insert<‘453453453’,‘John’,‘M’,‘1990-12-12’,‘spouse’>intoDEPENDENT.

    (f) Delete the WORKS_ON tuples with Essn = ‘333445555’.

    (g) Delete the EMPLOYEE tuple with Ssn = ‘987654321’.

    (h) Delete the PROJECT tuple with Pname = ‘ProductX’.

    (i) Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5 to ‘123456789’ and ‘2007-10-01’, respectively .

    (j) Modify the Super_ssn attribute of the EMPLOYEE tuple with Ssn = ‘999887777’ to ‘943775543’.

    (k) Modify the Hours attribute of the WORKS_ON tuple with Essn = ‘999887777’ and Pno = 10 to ‘5.0’.

Figure 5.6

   


  1. (2 marks) Consider the following relations for a database that keeps track of automobile sales in a car dealership (OPTION refers to some optional equipment installed on an automobile):

    • CAR(Serial_no, Model, Manufacturer, Price)
    • OPTION(Serial_no, Option_name, Price)
    • SALE(Salesperson_id, Serial_no, Date, Sale_price)
    • SALESPERSON(Salesperson_id, Name, Phone)

    First, specify the foreign keys for this schema, stating any assumptions you make. Next, populate the relations with a few sample tuples, and then give an example of an insertion in the SALE and SALESPERSON relations that violates the referential integrity constraints and of another insertion that does not. You also need to use the DB modeling tool `ERDCloud’ for this problem. Please refer to Problem 3 on how to use the tool.