Riyadh Philanthropic Society For Science Prince Sultan College For Woman

1. Riyadh Philanthropic Society For Science Prince Sultan College For Woman Dept. of Computer & Information Sciences CS 340 Introduction to Database Systems (Chapter 3 Practice Exercises)

2. Exercise 6 • Consider the following information about a university database: • • Professors have a SSN, a name, an age, a rank, and a research • specialty. • • Projects have a project number, a sponsor name (e.g. NFS), a • starting date, an ending date, and a budget. • • Graduate students have a SSN, a name, an age, and a degree • program (e.g., M.S. or Ph.D.). • • Each project is managed by one professor (known as the project's • principal investigator). • • Each project is worked on by one or more professors (known as the • project's co-investigators). • • Professors can manage and/or work on multiple projects. Chapter 3 Practice Exercises 16

3. Exercise 6 • Each project is worked on by one or more graduate students (known as the project's research assistants). • When graduate students work on a project, a professor must supervise their work on the project. Graduate students can work on multiple projects, in which case they will have a (potentially different) supervisor for each one. • Departments have a department number, a department name, and a main office. • Departments have a professor (known as the chairman) who runs the department. • Professors work in one or more departments, and for each department that they work in, a time percentage is associated with their job. Chapter 3 Practice Exercises 17

4. Exercise 6 • • Graduate students have one major department in which they are • working on their degree. • • Each graduate student has another, more senior graduate student • (known as a student advisor) who advises him or her on what • courses to take. • Draw an ER diagram that captures the information about the university. Chapter 3 Practice Exercises 18

5. Exercise 6 (solution) SSN PName Age Rank PNo Sponsor SDate EDate MANAGES 1 N PROFESSOR PROJECT Budget WORKS_ON M N 1 M M Specialty M 1 RUNS WORKS_IN SUPERVISES WORKS -ON2 N N Time 1 N 1 senior ADVISES 1 MAJOR N DEPARTMENT GRAD_STUDENT Graduate N DNo DName Office SSN SName Age Degree Chapter 3 Practice Exercises 19

6. Exercise 4 • Identify the entities and relationships for the following description • and draw an ER diagram. • A library system contains libraries, books, authors and patrons, with • identifying attributes library Number, book number, author number, • and patron number, respectively. Libraries are further described by a • library name and location, books by title and page count, authors by • author name, and patrons by patron name and patron weight. Libraries • hold numerous books (some with multiple copies), each authored by • one or more authors. Patrons borrow books, but at any point in time, • may not have anything checked out. When they do have a book • checked out, there is a due date associated with it. Chapter 3 Tutorial 5

7. Exercise 4 (solution) LibNo LibName BookNo Title 1 N HOLDS LIBRARY BOOK PageCount M N Location WRITTEN_BY CHECK -OUT DueDate AuthorNo N 1 PATRON PatronName AUTHOR AuthorName PatronNo PatronWeight Chapter 3 Tutorial 6

8. Exercise 5 • A loan company wants to design a database to track student loans. • Each student attending school is eligible for a loan. A student may • have more than one loan. A student may be registered, possibly at • different times, in more than one school. Each loan should belong to • only one bank. Each bank can approve as many loans as it desires. For each loan, the loan company will track: the student’s SSN, name, • address, amount of loan, date of loan, interest rate ( which may be • different for each loan as determined by the bank), duration of loan, • monthly payment, remaining balance, school ID, school name and • address, number of years the student has been at the school, bank • name, bank branch and bank ID. • Draw an ER diagram. • State any assumptions you make in the diagram. Chapter 3 Tutorial 7

9. Exercise 5 (solution) Assumption: each loan to a student (for different schools) is given a unique LoanId number. SSN SName LoanId Date Amount 1 N BORROWS STUDENT LOAN Payment Duration M SAddress N Interest ATTENDS APPROVED_BY NoOfYears Balance SchoolName N 1 SCHOOL BANK BankName SchoolId SchoolAddress BankId BankBranch Chapter 3 Tutorial 8

10. (1,50) (1,5) (1,10) (1,1) 1. Each department can have (1-50) employee(s). 2. Each phone is used by only 1 department. 3. Each phone is assigned to (1-10) employee(s). 4. Each employee is assigned (1-5) phone(s). • Exercise 2 • Consider the ER diagram below. Assume that an employee may work • in up to two departments or may not be assigned to any department. • Assume that each department must have one and may have up to three • phone numbers. Supply (min, max) constraints on this diagram. • State clearly any additional assumptions you make. (0,2) WORKS-IN EMPLOYEE DEPARTMENT (1,3) HAS-PHONE CONTAINS PHONE Assumptions: Chapter 3 Tutorial 3

11. M N TREATS DOCTOR PATIENT 1 N HAS HOSPITAL ROOM M N WRITES AUTHOR BOOK • Exercise 3 • For each of the following pairs of rules, draw an ER diagram showing the cardinality and participation constraints: • A doctor may treat many patients. A patient may have a designated • doctor for different types of problems. • A hospital has many patient rooms. Each patient room belongs to • that hospital. • An author may write many books. A book may be written by more • than one author. Chapter 3 Tutorial 4

12. Exercise 6 • Consider the following set of requirements for a university database • that is used to keep track of students’ transcripts. • a. The university keeps track of each student’s name, student number, • SSN, current address and phone, permanent address and phone, • birth date, sex, class (freshman, sophomore,...,graduate), major • department, minor department (if any), and degree program • (B.A.,...).Some user applications need to refer to the city, state, and • zip code of the student’s permanent address and to the student’s • last name. Both SSN 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. Chapter 3 Tutorial 9

13. Exercise 6 • c. Each course has a course name, description, course number, • number of semester hours, level, and offering department. The • value of the course number is unique for each course. • d. Each section has an instructor, semester, year, course, and section • number. The section number distinguishes 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, or 4). • Design an ER schema for this application, and draw an ER diagram • for that schema. 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. Chapter 3 Tutorial 10

14. Exercise 6 (Solution) Chapter 3 Tutorial 11