Assignment
Company Name: Motown Records
Define the relational database model and determine if a given database satisfies the properties of a relational database.
Write SQL queries to solve information retrieval problems of moderate complexity using a given relational database.
Length: Paper of 4-5 pages (excluding references and cover page) + Database System Plan template.
Scholarly references and articles only.
Write an Entity Relationship (ER) Diagram document of 4-5 pages based on the conceptual model that you completed. Include the following:
• Use Visio software application to create an ER diagram that outlines the primary keys for all entities, the relationships between different entities, the attributes (and data types) for each entity, and junction tables to support any many-to-many relationships
• A design that meets the minimum of third normal form (3NF)
• A description of the steps that you took to ensure that your design was normalized to the desired level and any exceptions you had to make to prevent anomalies and a loss in data integrity
• Add section to the Entity Relationship (ER) Diagram section of your database system plan
• Name the document as follows:
• All sources should be cited both in the text and in the References section by using APA format.
Assignment Objectives
Write SQL queries to solve information retrieval problems of moderate complexity using a given relational database.
Length: Paper of 4-5 pages (excluding references and cover page) + Database System Plan template.
Scholarly references and articles only.
Key Assignment Draft
You have been asked to develop a database system plan for a local company (or a company of your choosing) that wants to track its customers/clients, appointments, billing, and employees. Structured query language (SQL) is used to create the database schema, maintain the database records, and perform administrative tasks. In addition to the Database System Plan template that includes section headings (project outline), submit a SQL Statements document of 4-5 pages based on the entity relationship (ER) model completed in Week 3. Complete the following:
• Include SQL statements that use data definition language (DDL) commands to create the database schema specified in the ER diagram created.
o Include indexes, constraints, and relationships.
• Include SQL statements that use data manipulation language (DML) to do the following:
o INSERT 15 records into each table.
o UPDATE 5 sets of records in each table using one specific criteria per update statement (e.g., EXACT MATCH, LIKE, BETWEEN, IN, or HAVING).
o DELETE 2 records from 1 table based on specific criteria (e.g., EXACT MATCH or LIKE).
• Create stored procedures to support retrieval of the data from the database, using SELECT statements to accomplish the following:
o Aggregate (group) functions (count, avg, min, max, sum)
- Write 4 queries using group functions.
o Group By
- Write 2 queries using the Group By, Having, and Order By syntax.
o Simple queries
- Write 4 queries that manipulate data in 1 table.
o Join queries
- Write 4 queries that join 2 or more tables.
• Add this section to the SQL Statements section of your database system plan.
• All sources should be cited both in-text and in the References section by using APA format.
Attachment:- Motown-Records.rar