Map the given ER to the relational model. You should include the primary keys and foreign keys. Submit a PDF file to Canvas. Submission must be typed. Note: For all assignments in this course, no hand-written submission will be accepted. UNIVERSITY DATABASE RoomNo ExamNo Result Building RoomID Capacity CLASSROOM N Conducted in M EXAM N Takes M Exam M SSN CourseNo name Course Name Department COURSE Has Section N SECTION First Name M Enrolls N STUDENT Address Last Name Section No Zip State Street City

Map the given ER to the relational model. You should include the primary keys and foreign keys. Submit a PDF file to Canvas. Submission must be typed. Note: For all assignments in this course, no hand-written submission will be accepted. UNIVERSITY DATABASE RoomNo ExamNo Result Building RoomID Capacity CLASSROOM N Conducted in M EXAM N Takes M Exam M SSN CourseNo name Course Name Department COURSE Has Section N SECTION First Name M Enrolls N STUDENT Address Last Name Section No Zip State Street City

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

Database
You will need to create the database tables and choose sensible data types for each column.Make sure to maintain entity and referential integrity, and implement all the above constraints.Since other developers work in parallel on other parts of the system, you will need to strictly follow the above specification to correctly implemented in your database. Otherwise, the other software will not be able to use your database. This means, one needs to be able to rely on all constraints being correct, the spelling of table and column names being as given, and that the data types that you chose will allow for sensible data to be added to the database in the future.For testing purposes, you are asked to insert the data specified below into the tables, but no other data except this.In short:1. You will need to create the tables as specified2. You will need to add the data specified Please use the 3.sql and implement your tables using the sketched CREATE TABLE commands. Add the necessary queries…
Implement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram: The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply…
Given the database schema below, write a relational algebra expression to get the count of records in the DEPENDENT relation group by Essn. Also, show the result of your query.
A DreamHome database has following tables. Branch (branchNo, street, city, postcode) Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) PrivateOwner (ownerNo, fName, lName, address, telNo) Which is the correct relational algebra to express the following query? List the city and street of all branches which assistant staff’s salary is not greater than 9000 and age is at least 31. ( ) $$\Pi_{branchNo,street,city}(\sigma_{position='assistant' \wedge salary≤9000 \wedge DOB>'1-Jan-1990'} (Staff \bowtie Branch))$$ ( ) $$\sigma_{branchNo,street,city}(\Pi_{position=’assistant’ \wedge salary≤9000 \wedge DOB>’1-Jan-1990’}(Staff \cap Branch))$$ ( ) $$\sigma_{branchNo,street,city}(\sigma_{position=’assistant’ \wedge salary<9000 \wedge DOB>31}(Staff \bowtie Branch))$$ ( ) $$\Pi_{branchNo,street,city}(\sigma_{position=’assistant’ \wedge salary≤9000 \wedge…
StayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice. For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address. For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate. For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.
Generate the table creation DDL for the schema. Consider the keysprimary and foreign.
Write SQL update statements to do the following on the database schema shown in Figure 1.i. Insert a new course, <‘Knowledge Engineering’, ‘CS4390’, 3, ‘CS’>.ii. Delete the record for the student whose name is ‘Smith’ and whose student number is 17.
Convert this ER model to a list of tablesProvide the queries that implement the database
convert it to relations that are normalization
The following three tables make up a simple reservation system for a small campground. The database should allow for a camper to make multiple reservations for future dates and for a camping spot to have several reservations for upcoming visits. Table: Camper CAMPER_NUMBER 1000 1001 1002 1003 CAMPER_LAST_NAME Jones Schmidt Williams Cooper CAMPER_FIRST_NAME Jamie Pat Clifford Amanda CAMPER_ADDRESS 1278 Essex Pl 4954 Spangled Way 956 Segull Lane P. O. Box 998877 CAMPER_CITY Birmingham El Paso Portland Portsmouth CAMPER_STATE AL TX ME OH CAMPER_ZIP_CODE 45251 79919 4108 45662 CAMPER_DRIVERS_LICENSE JJ998743-98 87632434 WIL885123 765A876B897 CAMPER_EMAIL jjones@somewhere.com patwonderfu34l@nowhere.net williams98342@foomail.com coopera@nowhere.net Table: Spot SPOT_NUMBER 101 102 103 104 SPOT_NAME The Pines The Glade Teardrop Spot Tent In Trees…
In anticipation of a change to the database, you are asked to provide a list of all products that have 'Man-Made' or 'Man Made' in the the composition text. (HInt: It is easiest to use LIKE in this instance.) In addition, you are requested to replace either 'Man-Made' or 'Man Made' with the word 'Synthetic'. Only include ProductIDs between 1050 and 1060. Show the following in your answer: ProductID, ProductName, Composition, and the modified composition field as 'NewComposition'. (HINT: This problem is a little tricky because you need to replace both 'Man-Made' and 'Man Made'. The way to do this is to use a replace within a replace.)