Homework 1

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Homework 1 (total 100 points)

Please paste diagrams and code scripts into this Word document and upload this Word file with your answers to the HW1 assignment folder on Blackboard. Check the file after uploading it.

1. (40 points) Create a database diagram in MySQL Workbench based on the following rules (choose the appropriate data type for each attribute).

Video Rental

The manager of the StayHome Videos shop requires you to create a database application system to assist with the administration of the shop. The requirements collection and analysis phase has provided the following requirements for the database.

· The video shop stocks videos that fall into the following categories - comedy, drama, horror, musical, science fiction, and children. Each category is identified by a unique identifier. A video can belong to many categories. For this HW, please consider each video as a unique DVD disk with a DVD disk video identifier. For example, one movie can have many DVD video disks with a separate unique identifier.

· The data stored on the videos for rent includes the video number, catalog number, title, certification, year of release, main actor/actress, purchase price, daily rental rate. The video number is created by the manager and uniquely identifies each video.

· The data stored on each member of the video shop includes member number, date joined, name (consists of first and last name), address (consists of street, city, state, zip code), and one telephone number.

· Each member may borrow an unlimited number of videos, with the checkout date and the return date on each video. Each video can be borrowed by many members over time.

· Data on suppliers of videos should also be stored, such as supplier number, name, address (consists of street, city, state, zip code), and one telephone number. Date of supply should also be tracked for each video (think about where to put date of supply as an attribute). Each video has only one supplier, but a supplier supplies many videos.

· You may specify all the relationships as non-identifying (except for many-to-many relationship)

· Save your MySQL diagram as image (File – Export - Export as png) and paste it here

Student Courses

· A student should register for at least one course (and can register for many courses) and a course should have one or more students

· A student table should have the following attributes: studentID as a primary key, and such attributes as firstName, lastName

· A course table should have the following attributes: courseID as a primary key, and such attributes as courseName, courseDescription

· Since the relationship between a student and a course is many-to-many, you need to add an intermediate table between them titled ‘Registration’ that would hold the appropriate foreign keys and an attribute Grade. For this ‘Registration’ table you need to correctly specify a composite primary key that consists of several foreign keys

· An instructor can teach one or many courses and a course is taught by at least one and at most one instructor

· An instructor table should have the following attributes: instructorD as a primary key, and such attributes as instructorFirstName, instructorLastName

· A classroom can be used by one or many courses, and each course is held in at least one and at most one classroom

· A classroom table should have the following attributes: classroomID as a primary key, and such attributes as classroomNumber, classroomCapacity

· Make sure all the foreign keys are in the correct tables

· You may specify all the relationships as non-identifying (except for many-to-many relationship)

· Save your MySQL diagram as image (File – Export - Export as png) and paste it here

3. (30 points) Create a code from scratch to create tables based on your previous question design (only for student courses, question 2) in MySQL Workbench, and run the code to create those tables (including the correct data types for each attribute). There is no need to insert any data into the tables but make sure all foreign keys and primary keys are correctly specified (similar to the lecture in week 2 and to the week2 self-paced Panopto video – look at scripts create_university.sql and insert_university.sql)

Please copy the whole SQL script into this Word doc and upload your answers to all 3 HW1 questions as a Word document to the Blackboard HW1 folder