@zareen said in CS614 Assignment 1 Solution and Discussion:
Assignment No. 1
Semester: Fall 2019
CS614 – Data Warehousing
Total Marks: 15
Due Date:
November 14, 2019
Objectives:
After completing this assignment the students will be able to:
• Identify Database entities from a given scenario
• Understand the database table structure
• Normalize a database table up to 2nd normal form
• De-normalize relationships using collapsing table technique
Instructions
Please read the following instructions carefully before submitting assignment:
It should be clear that your assignment will not get any credit if:
o Assignment is submitted after due date.
o Submitted assignment does not open or file is corrupt.
o Assignment is copied (From internet/ to from students).
o Assignment is submitted other than word format (.doc, .docx).
Assignment
Question No. 1
Consider the following schema related to a Social Media website named as ‘userPosts’. You have to perform following tasks related to the provided schema:
1- Identify appropriate keys for following structure (Primary and/or foreign key(s))
2- Convert this schema into 2 NF
userPosts (userID, userName, password, address, postId, postDate, postContent)
Question No. 2
Consider the following schemas relevant to a hotel booking website. You are required to De-Normalization the given schemas using Collapsing Tables Technique.
roomVisitor (roomID, visitorCNIC, dateTime)
roomCharges (roomID, spentDays, roomRent)
Deadline:
Your assignment must be uploaded on VULMS on or before November 14, 2019. While November 15, 2019 will be a bonus day for assignment submission. After the bonus day, no assignment would be entertained via email.
Q.1 Answer:
userDetails (userID, userName, password, address)
posts(postID, postDate, postContent)
userPosts (userID, postID)
• userID and postID in schema / table ‘userPosts’ are foreign keys
Q.2 Answer:
roomVisitorCharges (roomID, visitorCNIC, dateTime, spentDays, roomRent)