Derive a physical design from the logical design

Assignment Help Database Management System

Reference no: EM133526527

Database Systems

Objectives
This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to improve students' skills to analyze organization database requirements, normalize the database and use data manipulation language to create, update and manage database. Furthermore, their research skill will be analyzed and application on the given case study.

This assessment covers the following

Learning outcome 1: Derive a physical design from the logical design taking into account application, hardware, operating system, and data communications networks requirements; further use of data manipulation language to query, update, and manage a database

Learning outcome 2: Identify functional dependencies, referential integrity, data integrity and security requirements; Further integrate and merge physical design by applying normalization techniques.

Learning outcome 3: Design and build a database system using the knowledge acquired in the unit as well as through further research on recent trends to demonstrate competence in various advanced tasks with regard to modelling, designing, and implementing a DBMS including Data warehousing, Data Management, DB Security.

Part B
Submit a single plain text file with filename as "studentid_studentname_AL_SQL" containing all SQL implementation. Your SQL queries must work on MS SQL Server and be able to be demonstrated.
SQL code required (Use MS SQL Server):

1. Create a database and CREATE TABLE statements for all tables in your ERD (Assignment 1) including primary and foreign keys.

2. INSERT INTO statements for populating the database
i. Insert five rows of (made-up) data into each table. Make sure that the data you enter in these tables should be sufficient to return at least bone row for each query in Task 3. AL should hold at least 5 bottles of Penfold Grange 2010 in some branch or other.

3. Select Statementsi. List the branches (ID) of MA that have in stock at least 5 bottles of Penfold Grange 2010.ii. SELECT statement to generate a list of all email addresses of members whose card will expire in the month after the coming month. Thus, for instance, if the query is run in November 2121, it will list the emails of all members whose membership will expire in January 2122. The emails should be ordered by Branch ID, then by expiry date, and then by the email address, all in ascending order.

Research and Discussion
Submitted as a MS Word or PDF Document

4. Consider the following relation schema as the join of a few tables from Assignment 1 ERD

Abnormal_Rel ( ProductID, BranchID, campaignID, MemberID, ProductType, PackageType, YearProduced, Price, Brand, StockLevel, CampaignStartDate, CampaignEndDate, FirstName, LastName, eMail, MembershipLevel, MemberExpDate, Discount
)

Determine for UPDATE anomaly whether or not the relation Abnormal_Rel is susceptible to that anomaly. Support your determination with adequate explanation and a small example.

5. Normalize/decompose the relation schema Abnormal_Rel until you get relations that are in 3NF. Use appropriate illustration to aid the understanding of your work.

Reference no: EM133526527

Questions Cloud

How does adopting ifrs increase complexity and uncertainty : Develop and explain a research hypothesis or proposition based on a theory, in relation to: how does adopting IFRS increase complexity and uncertainty associate

What is the main evidence offered for existence of free will : What is the main evidence offered for the existence of free will, and why is this evidence insufficient to justify our belief in free will?

Create a short fraud examiners checklist similar to appendix : Create a short Fraud Examiner's checklist similar to Appendix B in your Fraud Examiners manual with 10 items you think you will need to research or gather.

Summarize how corrective coding initiative : Summarize how Corrective Coding Initiative, Compliance strategies, and reporting and audit processes relate to the overall process of RCM.

Derive a physical design from the logical design : Derive a physical design from the logical design taking into account application, hardware, operating system, and data communications networks requirements

Describe the reporting units that compose the consolidated : Briefly describe the reporting units that compose the consolidated entity of United Way of America. How are they operationally interrelated?

Certified child life specialist : What is the role of a Certified Child Life Specialist (CCLS) in mass casualties?

How much do you expect to have in the account at the end : Assume that you invest $2749 per year in an account that is expected to average 7.8% return per year for the next 30 years. How much do you expect to have

What is the present value of the bond if the discount rate : The bond will pay no interim coupon payment. What is the present value of the bond if the discount rate is 6.5%?

User Account

All Pages