Assignment #4 (1)
docx
keyboard_arrow_up
School
Humber College *
*We aren’t endorsed by this school
Course
2030
Subject
Information Systems
Date
Apr 3, 2024
Type
docx
Pages
4
Uploaded by DeanKoalaMaster1037
Assignment #4
Part 1)
You are required to propose and present a database using an ER diagram for an online grocery
store having the following entities:
Customer
Department
Shopping cart
Payment
1. Make reasonable assumptions regarding attributes and relationships and list them. Write
down your assumptions.
2. Take a screenshot of the ER diagram and paste it here.
Part 2)
The database in the following figure is composed of two tables. Use this figure to answer
questions following questions. 1.
Identify the primary key for each table.
2.
Identify the foreign key in the PLAY table.
3.
Identify the candidate keys in both tables.
4.
Draw the ER model.
5.
Does the PLAY table exhibit referential integrity? Why or why not?
Part 3)
1.
Create a database diagram that shows the relationships between the six tables in the MurachCollege database. (The Tuition table is not related to the other five tables.)
2.
Design a database diagram for a database that stores information about the downloads that users make from a book website.
Each user must have an email address, first name, and last name.
Each user can have one or more downloads.
Each download must have a filename and download date/time.
Each book can be related to one or more downloads.
Each book must have a name.
Part 4)
1.
Write a script that implements the following design in a database named MyBookDB:
In the Downloads table, the UserID and BookID columns are the foreign keys.
Include a statement to drop the database if it already exists.
Include statements to create and select the database.
Define the BookName column so its value is unique.
Include indexes for the two foreign keys.
3.
Write a script that adds rows to the database that you created in exercise 1.
Add two rows to the Users and Books tables.
Add three rows to the Downloads table: one row for user 1 and Book 1; one for user 2 and Book 1; and one for user 2 and book 2. Use the GETDATE function to insert the current date and time into the DownloadDate column, and include data for each of the other columns.
Write a SELECT statement that joins the three tables and retrieves the data from these tables like this:
Sort the results by the email address in descending order and the book name in ascending order.
4.
Write an ALTER TABLE statement that adds two new columns to the Books table created in exercise 1.
Add one column for book price that provides for three digits to the left of the decimal point and two to the right. This column should have a default value of 59.50.
Add one column for the date and time that the book was added to the database.
Part 5)
1.
Use the Management Studio to create a new database named MyBookDB using the default settings. (If the database already exists, use the Management Studio to delete it and then recreate it.)
2.
Use the Management Studio to create the following tables and relationships.
3.
Define the UserID column in the Users table, the BookID column in the Books table, and the
DownloadID column in the Downloads table as primary keys and identity columns.
4.
In the Downloads table, set the UserID and BookID columns as the foreign keys.
5.
Define the columns so none of them allow null values.
6.
Use the Management Studio to create indexes for the foreign keys in the Downloads table. 7.
Use the Management Studio to create a unique index on the EmailAddress and BookName columns and a regular index on the DownloadDate column.
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
8.
Write an ALTER TABLE statement that modifies the Users table created in exercise 1 so the
EmailAddress column can store a maximum of 25 characters.
Code another UPDATE statement that attempts to insert an email address that’s longer than
25 characters. It should fail due to the length of the column.
9.
Write an ALTER TABLE statement that modifies the Users table created in exercise 1 so the
EmailAddress column must be unique.
Code an UPDATE statement that attempts to insert a nonunique value into this column. It should fail due to the unique constraint.
Assignment #4 Submission
Due date: check the blackboard.
The students are encouraged to contact the instructors by email and book appointments to
discuss the aspects of the assignments and any other concerns.
Good Luck,