Assignment No. 1
Semester: Fall 2019
CS614 – Data Warehousing
Total Marks: 15
November 14, 2019
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
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).
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)
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.
userDetails (userID, userName, password, address)
posts(postID, postDate, postContent)
userPosts (userID, postID)
• userID and postID in schema / table ‘userPosts’ are foreign keys
roomVisitorCharges (roomID, visitorCNIC, dateTime, spentDays, roomRent)