CSIT555_hw1

docx

School

Montclair State University *

*We aren’t endorsed by this school

Course

CS555

Subject

Computer Science

Date

Feb 20, 2024

Type

docx

Pages

5

Uploaded by PresidentTeamOkapi31

Report
Q1. ER Design [20pts] A university DB contains information about professors (identified by SSN) and courses (identified by course ID). Professors teach courses; each of the following situations concerns the Teaches relationship set. For EACH situation in (a) and (b) below, draw an ER diagram that describes it, and list all candidate keys of the Teaches relationship set. (a) Professors can teach the same course in several semesters, and each offering must be recorded [5pts] . (b) Professors can teach the same course in several semesters, but only the most recent such offering needs to be recorded [5pts] . Assume the above Situation (b) applies in all subsequent situations. Then, draw an ER diagram that describes each of the following situations: (c) Each professor teaches at least one course, and each course is taught by at least one professor [5pts] . (d) Every professor teaches exactly one course, and every course is taught by exactly one professor [5pts] . Professor ssn courseId Teaches Course semesterid Semester Professor ssn courseId Teaches Course semester Professor ssn courseId Teaches Course semester
Q2. ER Design + DDL [50pts] You are hired to set up a relational database for a small community hospital. Below are the facts of the hospital’s data. Every room has a unique room number (integer); Every room has one designated usage (varchar[40]), but different rooms may have the same usage; Every patient is assigned a room, but multiple patients may be assigned to the same room; Every patient has a unique patient number (integer); Every patient has a name (varchar[40]) which is not necessarily unique; A patient may be treated by more than one doctor, and a doctor may attend to more than one patient; Every doctor has a unique doctor ID (integer) and a unique phone number (varchar[10]). Question: (a) Draw an entity-relationship diagram to represent the information described above [25pts] Professor ssn courseId Teaches Course semester
(b) Suppose every entity set and every relationship set is to be represented by a different relation. Give the SQL CREATE TABLE statements for those relations that represent each entity and relationship set [25pts] . Answer: CREATE TABLE Room ( RoomNumber INT PRIMARY KEY, Usage VARCHAR(40) ); CREATE TABLE Patient ( PatientNumber INT PRIMARY KEY, Name VARCHAR(40) ); CREATE TABLE Doctor
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
( DoctorID INT PRIMARY KEY, PhoneNumber VARCHAR(10) ); CREATE TABLE Treats ( RoomNumber INT, PatientNumber INT, DoctorID INT, PRIMARY KEY (RoomNumber, PatientNumber, DoctorID), FOREIGN KEY (RoomNumber) REFERENCES Room(RoomNumber), FOREIGN KEY (PatientNumber) REFERENCES Patient(PatientNumber), FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID) ); Q3. DDL [30 pts] Consider a relational database for an online hotel booking company. The database consisting of the following tables (where the primary keys are underlined): Hotel (hId , hName, hAddress, hCity) Guest(gId , gName, gAddress, gCity) Room(hid, roomNo , type, price) Booking(gId, hId, roomNo, fromDate, year , noOfDays) Where hId and gId are identifiers for the hotels and the guests, and the Booking relation indicated that a guest booked a hotel room for a specified number of days (noOfDays) starting from fromDate of a given year. For instance, a tuple < g12345, h5555, 220, Jan05, 2016, 15> in Booking indicates that the guest g12345 booked room 220 of the h5555 hotel for 8 days starting on Jan 5, 2016. The attribute "gAddress" records the home address of the guests. Write the following queries in relational algebra [5*6pts=30pts]: Answer: (1) Return the name of guests who have booked a hotel room in NYC in January 2016 (i.e., fromDate between 01/01 and 01/31 and year=2016).
π gName(σ gCity='NYC' and fromDate>=Jan01 and fromDate<=Jan31 and year=2016 (Guest ⨝ Booking)) (2) Return the name of guests who have booked a hotel room of type “suite” in NYC for longer than 30 days. π gName(σ type='suite' and noOfDays>30 and gCity='NYC' (Guest ⨝ Booking ⨝ Room)) (3) Return the name of guests who never booked a hotel room for longer than 3 days. π gName(Guest - (Guest ⨝ Booking ⨝ σ noOfDays>3 (Room))) (4) Return the ids of the hotels located in NYC which were not booked at all in the year 2015. π hId(σ gCity='NYC' and year=2015 (Hotel - (Hotel ⨝ Booking))) (5) Return the ids of the guests who have booked at least one room of type “penthouse suite” in every hotel located in NYC. π gId((Guest ⨝ Booking ⨝ σ type='penthouse suite' and gCity='NYC' (Room)) ÷ hId)

Related Documents

ECE 563 - Project 1.docx
15523073.docx
Decision_Trees_Practice_Questions.pdf
Homework 6.docx
Homework 4.docx
Module 3 Homework Assignment (1).pptx
Module 5 Homework Assignment (1).pptx
Heffron_Joseph_Lab_05.docx
Subnet Questions -solution.doc
comp_clean_W2.pdf
Final Class Summary Response- Anna Reid Baer.doc
Lab4 301308686.docx
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
SEE MORE TEXTBOOKS