Database Fundamentals- MySQL

User Generated

Qrfvtny2

Computer Science

IT 214

George Mason University

Description

Project Description:

Part II is 40% of the total grade of the project. You must use MySQL to implement the database you designed in Part I. Test the queries you used to create and populate the database in MySQL. After successful completion of an SQL query:

  • Type your name at the following mysql> prompt.
  • Take a screen shot (Alt + Print Screen) that includes the query, the confirmation message, the results (if any), and your name.
  • Paste the screen shot in the appropriate location in your MS Word file.

IMPORTANT NOTICE: If some of the tables in your database have the same names as the tables in the databases used in class (Premiere Products, Henry Books, and Alexamara Marina Group), re-name your tables. For instance, if the name of your table is CUSTOMER, change it to CUSTOMER_1. Do not delete existing tables from the databases used in class otherwise you will not be able to complete the rest of the homeworks.

Part II must include:

Paste all screen shots from parts a-d for each table together in one place.

  1. A query that is based on two tables and includes both an arithmetic and a special operator in the conditional expression.
  2. A query that is based on more than two tables and includes both an arithmetic and a special operator in the conditional expression. Both the arithmetic and the special operator must be different from the ones used in part a.
  3. A query that includes a sub-query.
  4. A query that contains the GROUP BY clause, one of the aggregate functions, the WHERE statement, and the HAVING operator.
  5. Two additional complex queries designed by you that are different from the above. A complex query is based on two or more tables and includes either several conditions or a subquery (or both).

Notes:

- I have attached the Part I of the project that you previously did a couple of weeks ago. Part 1 is needed to complete this part of the project.

Unformatted Attachment Preview

Running head: RETAIL STORE RELATIONAL DATABASE Retail Store Relational Database Database Fundamentals IT 214-001 Tatheer Akbar 1 RETAIL STORE RELATIONAL DATABASE 2 Department store relational database The department store is a retail store that has expanded by opening more stores within the local area. The store will have a database that can accommodate the sales and inventory data for the store. The database design will align with the product sales initiatives of the company. Business Requirements The Departmental Store Database should store all data obtained from sales transactions that start from when a customer brings items to the store register. The store salesperson records the details of specific items using the scanner. The database should provide a list of all products sold at the retail stores, a list of all suppliers who provide items to the store as well as all transactions associated with sales. The store's databases should store customer data, the stores’ data, customer requests data and products data. For example, the expected sales can be obtained by summing consumer sales to determine the strength of the sales volume. RETAIL STORE RELATIONAL DATABASE Business Rules 1. All products have common attributes (Product Identification, Product Name, Product Category, Product Price), some products have unique attributes (like product code, and manufacture date). 2. Each Product could be supplied by many suppliers but each Product must have at least one Supplier’s name associated with it. Each supplier could supply many products but every supplier must supply at least one product. 3. A customer may have many requests and a customer request may have several items. 4. A store may get many requests. Department store database entities • Customers • Products • Vendors • Employees • Store • Sales Entities and Attributes Entity #1: CUSTOMER Customer_ID : [PK] ATTRIBUTE Type:INTEGER Customer_Contact : ATTRIBUTE Type:INTEGER Customer_FName : ATTRIBUTE Type:VARCHAR Length=255 Customer_LName : ATTRIBUTE Type:VARCHAR Length=255 Customer_Address : ATTRIBUTE Type:VARCHAR Length=255 3 RETAIL STORE RELATIONAL DATABASE Entity #2: PRODUCT Product_ID : [PK] ATTRIBUTE Type:INTEGER Product_Category : ATTRIBUTE Type:VARCHAR Length=255 Product_Price : ATTRIBUTE Type:FLOAT Product_Name : ATTRIBUTE Type:VARCHAR Length=255 Entity #3: SALES Sales_ID : [PK] ATTRIBUTE Type:INTEGER Customer_ID : ATTRIBUTE Type:INTEGER Employee_ID : ATTRIBUTE Type:INTEGER Product_ID : ATTRIBUTE Type:INTEGER Date : ATTRIBUTE Type:TIMESTAMP Entity #4: STORE Store_ID : [PK] ATTRIBUTE Type:INTEGER Store_Name : ATTRIBUTE Type:VARCHAR Length=255 Store_Address : ATTRIBUTE Type:VARCHAR Length=255 Store_Location : ATTRIBUTE Type:VARCHAR Length=255 Store_Contacts : ATTRIBUTE Type:INTEGER Entity #5: VENDOR Vendor_ID : [PK] ATTRIBUTE Type:INTEGER Company_Name : ATTRIBUTE Type:VARCHAR Length=255 Store_ID : ATTRIBUTE Type:INTEGER Product_ID : ATTRIBUTE Type:INTEGER Company_Location : ATTRIBUTE Type:VARCHAR Length=255 4 RETAIL STORE RELATIONAL DATABASE 5 Company_Contact : ATTRIBUTE Type:INTEGER Cost : ATTRIBUTE Type:FLOAT Entity #6: EMPLOYEE Employee_ID : [PK] ATTRIBUTE Type:INTEGER Employee_LName : ATTRIBUTE Type:VARCHAR Length=255 Employee_FName : ATTRIBUTE Type:VARCHAR Length=255 Employee_Address : ATTRIBUTE Type:VARCHAR Length=255 Entities and Relationships: Buys : REGULAR RELATIONSHIP Customer ONE MANDATORY to Product MANY OPTIONAL Has : REGULAR RELATIONSHIP Store ONE MANDATORY to Product MANY OPTIONAL Supplies : REGULAR RELATIONSHIP Vendor ONE MANDATORY to Store MANY OPTIONAL Has : REGULAR RELATIONSHIP Sales ONE MANDATORY to Product MANY MANDATORY Makes : REGULAR RELATIONSHIP Employee ONE MANDATORY to Sales MANY MANDATORY RETAIL STORE RELATIONAL DATABASE Department store Entity Relationship Diagram (ERD): 6 RETAIL STORE RELATIONAL DATABASE References Dennis, A., Wixom, B. H., & Roth, R. M. (2009). Systems analysis and design. Hoboken, NJ: Wiley. 7
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hey I am through. Everything is in the attached word file. Thank you.

Running head: RETAIL STORE RELATIONAL DATABASE

Retail Store Relational Database

Database Fundamentals
IT 214-001
Tatheer Akbar

1

RETAIL STORE RELATIONAL DATABASE

2

1. A query that is based on two tables and includes both an arithmetic and a special operator
in the con...


Anonymous
Awesome! Made my life easier.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags