Q5- Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course. Draw a relational schema diagram specifying the foreign keys for this schema. STUDENT (SSN, Name, Major, Bdate) COURSE (Course#, Cname, Dept) ENROLL (SSN, Course#, Quarter, Grade) BOOK_ADOPTION(Course#, Quarter, Book_ISBN) TEXT(Book ISBN, Book_Title, Publisher, Author) Hand drawing pot allowed

Q5- Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course. Draw a relational schema diagram specifying the foreign keys for this schema. STUDENT (SSN, Name, Major, Bdate) COURSE (Course#, Cname, Dept) ENROLL (SSN, Course#, Quarter, Grade) BOOK_ADOPTION(Course#, Quarter, Book_ISBN) TEXT(Book ISBN, Book_Title, Publisher, Author) Hand drawing pot allowed

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

write queries in relational algebra
Database Lab (CE 450) ( Task 9) B) Objective: Design the Appropriate Database Schema for ER diagram: 1. Identify the tables for the Database. 2. Identify the columns for the tables. 3. Identify the constraints(PRIMARY KEY,UNIQUE,NOT NULL,FOREIGN KEY ) constraints for the columns. An ER diagram for a BANK database schema BRANCHES BANK_BRANCH BANK Code Name Addr Addr Branch_no АСCTS LOANS Acct_no Balance Loan_no Amount ACCOUNT Туре Туре LOAN M A_C LC (Tname Mname Lname Bdate Ssn Name Age Phones CUSTOMER Addr
The database used for this question is a very simple one with the following schema: (Primary keys are bold, foreign keys are underlined) CUSTOMER (CustID, FirstName, LastName, City, Phone, Email) INVOICE (InvoiceNumber, CustID, Date) INVOICE_ITEM(InvoiceNumber, ItemNumber, Quantity) ITEM (ItemNumber, ItemName, UnitPrice) When a customer makes a purchase, an invoice is created. The invoice may be for many items. For example, in a single purchase, a customer might buy 10 Back Scratchers, 4 Hair Removers and a Dog Lead. Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks. NOTE: You can use the symbolss, P, etc., or the words’ PROJECT’, ‘RESTRICT’etc.as you prefer. You do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition. List the first and last names of Customers who come from the City named Perth. List the first and last names of customers who had…
When mapping entities to one another for a relational database, there are several different approaches to take. Describe your thought process when trying to determine the mapping relationship (I.e. 1:M, M:N, 1:1) of two entities.
Indicate the changes (using the shorthand representation) that you would need to make to the original KimTay Pet Supplies database design to support the following requirements. There is no relationship between customers and sales reps. When a customer places an order, any sales rep can process the order and create the invoice. On the invoice, you need to identify both the customer placing the order and the sales rep responsible for the invoice. Draw an E-R diagram for the new design.
Doctors have id. name. date of birth. and specialty. Patients have id. name, and date of birth. and address. Medicines have name. patent number, and milligrams We want to build A database that keeps track of the medicines prescribed by a doctor to patients The prescription process happens in a hospital. (a) Draw a complete Entity-Relationship diagram for the description above. You must include all the details, such as keys, weak entities (if any). cardinality constraints., etc Your diagram must use n-ary relationships You must use arrow notation Your diagram must avoid redundancy.
The database used for this question is a very simple one with the following schema: (Primary keys are bold, foreign keys are underlined) CUSTOMER (CustID, FirstName, LastName, City, Phone, Email) INVOICE (InvoiceNumber, CustID, Date) INVOICE_ITEM(InvoiceNumber, ItemNumber, Quantity) ITEM (ItemNumber, ItemName, UnitPrice) When a customer makes a purchase, an invoice is created. The invoice may be for many items. For example, in a single purchase, a customer might buy 10 Back Scratchers, 4 Hair Removers and a Dog Lead. Provide relational algebra (NOT SQL) queries to find the following information. NOTE: You can use the symbols s, P, etc., or the words’ PROJECT’, ‘RESTRICT’ etc. as you prefer. You do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition. List the dates on which Homer Griffin made purchases. List the first and last names of customers who have bought “Back Scratcher” or “Hair Remover” List the first…
Design a Relational Database by Mapping the following Entity-Relationship Diagram (ERD) into Relational Model. You must clearly determine the PK of each relation. Also, you should show the links between FKs and PKs in your answer. SName Owner SHIP MOVEMENT PName HISTORY HOME PORT SHIP PORT Latitude (0,") VISITS (0.") Timestamp Longitude N N N (1.1) IN TYPE ON 1 1 Date Time StartDate VISITS Type EndDate Tonnage SHIP TYPE Name STATE/COUNTRY Name SEA/OCEAN/LAKE Hull Continent
If you sell a house more than once, it is treated as different "property" for each sale. Draw an entity-relationship diagram and write a database design outline. Q2. Law Office: You are trying to automate the records for your uncle's law firm. Multiple attorneys work for the firm, and for each of them you want to store their first name, last name, date hired, hourly billing rate, and date promoted to partner (blank if the attorney is not a partner). Each attorney has multiple cases, which is identified by a unique "docket number". Each case has a name and description, is worked on by only one attorney, and involves a single client. For each client, you want to store a first name, last name, middle name, phone number, address, city, state, and zip code (assume you do not have a zip-code table). Finally, you want to keep track of the billable hours spent on each case. For a single case, this comprises information that looks like Hours Billed Date 2/24/2005 Description Prepare brief…
The entity relationship diagram below models the components of a database belonging to the South African Film Writers' Association. The database allows SAFWA to keep track of movies filmed in South Africa, developed by domestic and international studios. The MS Access database diagram below shows the tables created for a travel agent company called TravelSpace. They implemented their database to keep track of their customer's bookings for the different trips they have available. The company offers multiple tours which can be booked by many different customers. Given the diagram below, answer the following questions by providing SQL statements. CUSTOMER BOOKING CUST NO CUST FNAME BOOKNG NO TOUR BOOKING EMP ID CUST NO BOOK STATUS CODE 7 TOUR ID 7 80OENG NO TOUR DATE TOUR CUST LNAme CUST ADORESS CUST DOB CUST EMAIL OUT SEAT NO IN SEAT NO CUST, PHONE 1 TOURO TOUR NAME TOUR DESCRIPTON EVENTJD HOTEL_D TOUR PRICE ADULT FUGHT NO BOOK, TOTAL, COST BOOKNG DATE TOUR PRICE CHILD TOUR, PRICE CON…
Consider the following five relations for an University database system: STUDENT (SSN, Name, Major_Department, Minor_Department, Bdate) DEPARTMENT (DepartmentName, Location) COURSE (Course#, Grade, OfferedDepartment) ENROLL (SSN, Course#, Grade) BOOK_ADOPTION (Course#, Book_ISBN) Please draw the relational schema diagram. I will be using this one as a comparison to the one that I made to make adjustments if needed. Subject: MYSQL
Consider the following relations for a simple airport databas Hangars (hangNo, capacity, location, status) AirplaneTypes (Model, capacity, weight) Airplanes (regNo, model, hangNO) Owners (owner, name, address, phone, ssn) Owns (regNo, ownID, startedDate) There are four foreign keys for this database: 1. The attribute hangNo of the relation Airplanes that references the relation Hangars, 2. The attribute model of the relation Airplanes that references the relation AirplaneTypes, 3. The attribute regNo of the relation Owns that references the relation Airplanes, and 4. The attribute ownID of the relation Owns that references the relation Owner. Q1: Write SQL statements to define the above relation Hangars. You should choose a suitable data type and/or daya size for each column and be sure to declare appropriate integrity constraint(s). Q2. Which of the following SQL statements is used to define the above relation Airplanes? Ensure the data type and size for each attribute are…