You will design a database that provides data storage for a business application used by a fictitious online cell phone applications retailer.
BuZZSoftCorp is an online provider of software applications for cell phones including ring tones, games, and other applications. Authors create the applications and submit them to BuZZSoftCorp for consideration. Customers purchase applications by credit card or electronic payment service and then download the applications locally. The database does not contain object code, but does store the physical path to the applications and its documentation. Authors are paid quarterly on a royalty basis.
NOTE: Royalty statements normally include an item breakdown. For the purposes of this project, you are concerned about quarterly royalty payments only, not item details.
The database should be documented through entity relationship diagrams. The database should be normalized. Entity integrity must be enforced on all tables.
Purpose:
• Creating tables
• Adding columns to an existing table
• Creating a diagram to show the logical design of the database
• Creating a triggerto guard against a SQL injection attack
• Executing queries to meet requirements
Deliverables, Requirements, and Timeline
Please note that you must provide a screen capture for every deliverable. You need to provide screen captures that prove your work. Your project will be graded based on the screen captures you provide. Be sure to submit all your screen shots as one word document to the appropriate drop box on learning studio.
1. Basic database requirements. Prepare a list of possible entities for the database. Include a list of values that must be tracked for each entity. Identify any tables that would have related values.
a. Does the entity list include all entities that you might need?
b. Is the list of values needed for each entity complete?
The entity list for this purpose is as follows:
Entity: Employee
Attributes: Last Name, First Name, address line 1, city, state, zip code, employee ID
Entity: Author
Attributes: : Last Name, First Name, address line 1, city, state, zip code, author ID, royalty payments
Entity: Customer
Attributes: Last Name, First Name, address line 1, city, state, zip code, customer ID
Entity: Software
Attributes: ID, Version, Revision date, Physical location
Entity: Royalties
Attribute: Author ID, Royalty date, Royalty amount
Entity: Order head
Attributes: Sales tracking, Customer ID, Employee ID
Entity: Order details
Attributes: Sales tracking, Item number, ID, Version, Quantity
3. Design entities using the free online ERD drawing software available at (https://www.draw.io/#LUntitled%20Diagram.xml . to manage software applications and sales orders. Each software object must track revision date and version number, as well as a physical path for download. Sales orders must include the customer taking the order, the employee placing the order, item or items ordered, and a sales tracking number. Create ER drawings using each table and establish the relationships between tables. (one to one, one to many, many to many..etc). Include the attribute or attributes to be used as primary key. Create as many additional tables as necessary.
Now, use MS SQL Server 2008 to create an ERD for the initial entity list. Identify a primary key for each entity and establish relationships between tables using foreign keys.
Course Project-Part II
Create scripts usable with SQL Server Express Edition to create the tables needed by your solution.
Execute the following to create a working database:
CREATE DATABASE ProjectData
Execute the scripts to create the tables in the ProjectData database.
Create a separate script for each table. The CREATE TABLE statement must include specification of the primary key. Use the syntax: CREATE TABLE tablename
-after creating all the seven tables as described below, enter 5 records of sample data (you will make up data for each table below) for each table so that you have a working database.
Employee table: Last Name, First Name, address line 1, city, state, zip code, employee ID
Customer table: Last Name, First Name, address line 1, city, state, zip code, customer ID
Author table: Last Name, First Name, address line 1, city, state, zip code, author ID, royalty payments
Software table: ID, Version, Revision date, Physical location
Royalties table: Author ID, Royalty date, Royalty amount
Order head table: Sales tracking, Customer ID, Employee ID
Order details table: Sales tracking, Item number, ID, Version, Quantity
Load sample data into the database tables using the syntax Insert into table name to add data to each individual table. Enter 5 records of sample data (you will make up data for each table) for each table so that you have a working database.
. Run queries to return the following information and provide screen shots of the results:
• All rows and columns for employees
• All rows and columns for customers
• A count of sales orders
• A list of customers who have placed orders
– Create a trigger on the customers table to capture the date, time, old customer name, and new customer name every time the customer table is updated.
TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!