Implement a relational database using the structured query

Assignment Help Database Management System

Reference no: EM133670527

Learning Outcome 1: Show an understanding of the fundamentals of database design, implementation, management, and maintenance.

Learning Outcome 2: Implement a relational database using the Structured Query Language (SQL) Data Definition Language (DDL).

Learning Outcome 3: Demonstrate an ability to query a relational database using Structured Query Language (SQL) Data Manipulation Language (DML).

Aim:

To give you practical experience in database modelling, normalization, relational database implementation, loading your group developed relational database script file, .sql to the PostgreSQL server, and writing SQL statements to query, to test or retrieve data information from a relational database.

The project specification details provided in this document, and you must use only given specifications to come up with your solution. Make appropriate assumptions wherever required.

Please make sure that your group must meet your lecturer/tutor at least twice to update the progress of your assignment.

Background Information for Sample Database Project:

Australian Manufacturing Company (AMC) requires a database system to manage their stores, employees, products, suppliers, patrons and orders.

The following information has been gathered about current business activities of AMC.

AMC has many stores in different cities across Australia. Each store has unique store number and other details like name, contact details (phone number, email address and fax number) and location details (postal address composed of street number, suburb, state and postcode).
There are many departments within each store, for example, HR, Accounts, Finance and Sales etc. each department is uniquely identified by department number. AMC also records department title, phone number and email address.
Each store has a number of employees. Each department is supervised by a department supervisor and each store is managed by a store manager who both are employees of AMC. Each department has a single supervisor only i.e. the department supervisor is also the supervisor for all the employees work within that department.
Each employee is referenced by unique employee number, name (first and last names), address, mobile number, email, TFN, salary and joining date. An employee's salary is recorded differently as per his or her employment type. Employees can be employed as Fulltime or Casual basis. For a Fulltime staff, annual salary is recorded and hourly rate for a Casual staff.
Each store creates payslips for all employees work in that store on a weekly basis. As a minimum payslip number, no of hours worked, employee detail, store detail and gross pay are recorded.
AMC sales different products like fashion, health, beauty, electronics etc. Each product is characterized by product number, name, brand, description and price.

Each product can be supplied by one or more supplier and each supplier can supply one or many products. AMC also keeps appropriate details of their suppliers.
Each store maintains an inventory of their products available for sale. The quantity of each product available in store and the quantity ordered are kept.
AMC patron visits the store and shops in the store. Each customer is characterized by customer number, name (first and last names), mobile number and address.
A patron may place orders. An order contains one or many products at a time. A store keeps track of order number, order date, product(s) ordered, and quantity ordered for each product.
The proprietors of AMC have appointed your group as Database System Consultant to analyze, design and develop conceptual data model and physical database. They want a software/online application in future to be built on the database you design so that the operation of the AMC is greatly improved.

Your created models must accommodate the provided requirements. It is understandable that the provided information may not sufficient to create model that centralize all the data appropriately. You are free to make assumptions about their business activities if require. Make sure you record your assumptions.

System Requirements:

The system is a prototype system and as such is not a full production version.

You will be required to use free Online App/Tool, to draw the Crow's Foot Notation Entity Relational Diagram (ERD), and save your group ERD as an editable file, .peg.
You also will be required to install the PostgreSQL on your own computer, to use the client, SQL Shell (psql) to load the implemented relational database script file .sql to the PostgreSQL database server, and to test the design and operation of your database.

Project Specification:

Part A: Entity Relationship Diagram
Your first task is to study the Case study requirements and gather as much knowledge to list all the main Entities and attributes.
It is required students to use Free Online App/Tool, and Crow's Foot Notation to Create an Entity Relationship Diagram (ERD) to help you decide on the relationships.
Your entity relation diagram that models your database design should:
Include all entities, relationships (including names) and attributes.
Identify primary and foreign keys.
Include cardinality/ multiplicity and show using crow's feet notation.
Include participation (optional / mandatory) symbols if applicable.
Note: The ERD should be created as an editable file .peg or .svg. Hand-drawn diagrams, photos or screenshots from mobile phone will not be accepted.

Part B: Normalization
Normalize all entities and relationships in optimal normal form. Provide the normalized schema and discuss the level of normalization achieved for each relation. You goal should be normalise all entities in 3NF. Provide the reasons for any relation that is not maintained in 3NF.

Part C: Relational schema and Implementation of the PostgreSQL database
Create relational schema that matches ERD and normalized schemas you created in Part A and B. The schema should include suitable data validation, integrity and referential integrity constraints, for example, types and length of attributes, primary and foreign keys etc. Make sure you follow standards, naming conventions and notation.

Include the relational schema in your documentation. The following is sample tabular format on relational schema. You are free to add more columns if required.

Entity/Table Field Name Datatype Constraints
EMPLOYEE employeeID int NOT NULL
PK
firstName varchar (30) NOT NULL
lastName varchar (30) NOT NULL
gender char (1) NOT NULL
dateBirth Date NOT NULL
email varchar (50)
phone char (10) NOT NULL
storeNo int NOT NULL
FK
departmentNo int FK
Implementation of the relational database for loading into the PostgreSQL database server includes creating the script file AMCDB.sql, showing how to create tables and any constrains to build relationships between tables, insert values into tables, e.g. use boygirl.sql file as an example to create tables, to link tables using constrains of Foreign Key, and to insert values into each table. In addition, your group should demonstrate that the implemented relational database script file AMCDB.sql can be successfully loaded into the PostgreSQL database server through the command line client, psql. (5 Marks)
Create tables according to your relational schema created in step 1.
you are required to develop a demonstration prototype system (database) that stores data in the PostgreSQL database server for AMC stores, employees, products, suppliers, patrons and orders. Create a new database called AMCDB.sql.
Follow a standard naming convention for table names and also field names. Avoid using spaces and any special characters in table and field names. Use the camelCase or underscore_case to separate parts of a name.
You are required to be consistent in tables' name, fields' name, data types,
field size and key constraints with relational schema created in step 2.

Use DESCRIBE command to describe table the structure of each table in your AMCDB database, e.g. \d employee

Insert values into tables, add at least five records into each table.
Demonstrate at least five records in each of your tables as shown by the example below:

Part D: SQL

It is required that students must complete Part D, by using the client, SQL Shell (psql) to load the implemented database script file, AMCDB.sql into the PostgreSQL database server for storing the relational database, and testing these designed questions in Part D.
List the full name of Patrons, phone number and address in the descending order of customer number. The full name is comprised of first and last name joined with single space. Use the alias Customer full name for the composed column heading.
List all the products having price less than $100.
List the product number, product title, quantity ordered, unit price and total amount per product (unit price * quantity ordered) for order having order number 1005.
List name, full address and fax number of stores that has the word ‘George' anywhere in the street component of address. Your query should consider the case sensitivity of the street as well i.e. the stores with words ‘George', ‘GEORGE', ‘george', ‘GeorGe' etc. in street should be returned by your query too.
List all the orders having at least two different products ordered.
List customer number, name and address of all the customers who has not placed any order.
Retrieve first name, last name and employee number of all the employees along with department name and store name where they work.
For each store, display store name and number of employees works in account department.
List all the orders placed for financial year 2018 i.e. orders placed after 30/06/2017 and before 01/07/2018.

Retrieve customer number and the total number of orders placed by each customer.
For each order, retrieve the order number, order date and number of products ordered in it. Sort the output in descending order of order number.

Your submission files should include both .sql/.txt file and the final report in Word document. Do not use screen captures to display the SQL statement or the output.
For the .sql/.txt submission file, you should right-click on the PostgreSQL Command Prompt Window:
To select all content, by using "ctrl + A" to choose all SQL statements and Outputs,
To Copy it by using "ctrl + C", then
To Paste into a text file by using "ctrl + V", and
To save it as .txt or .sql file that should include the whole copy of the output from the psql as follows:

Login to the psql
To store the implemented relational database script file, AMCDB.sql into the PostgreSQL Database server through the client, psql through the command: \i to load the script file
To test each designed Question in Part D, and execute SQL queries that answer
these questions in Part D;
SQL statements and its output that is produced when you execute the statement in your database for each question in Part D. The output includes the records that are listed and also the message that appears when you run the SQL statement.

In your final report, the answer to each question must be as follows:
Number your answers to each question clearly. The answer to each question must be tabulated as shown in the example below and include the SQL statement and also the output that is produced when you execute the statement in your database. The output includes the records that are listed and also the message that appears when you run the SQL statement.

For Example:
Question 1:

Do not use screen captures to display the SQL statement or the output.
You should right-click on the PostgreSQL Command Prompt window; choose SQL statement and Output, to Copy and then Paste into your final report that includes the answers to all questions.
Format and indent the clauses in your SQL statements for better readability and

understanding as shown in the example above. Statements must be syntactically and semantically correct.

Format both the SQL and also the Output in Courier New 8 or 10 point.

Part E: Individual Personal Report, Presentation slides, 5 minutes Video Presentation, and peers review feedback & results

The individual Presentation slides and 5 minutes Video Presentation (8 marks) should include information as follows:
The title page should include student's name, ID and the link of your YouTube Video.
The 1st slide describes the project domain modelled by this database. One slide could be enough. The audience just need to know enough about the domain to understand the ERD.
The 2nd slide includes the complete ERD for this database and give an example how to solve Many-to-Many relationship in your ERD.
The 3rd slide includes the illustration a single One-to-Many relationship in the database. This slide should show both the relationship as shown in the ERD and its realizations in tables, with some example values in the tables.
The 4th slide shows one query and output of Part D in Group Project Outline.
The last slide shows your contributions, any challenging issues, or any future work.

One page of personal report (2 marks): Write a page to the AMC proprietors as a personal reflection that describes your experience building the database, and your contribution to your group project. You can discuss any challenges / difficulties that you experienced or solutions that you found. Comment on any limitations and / or strengths of your database design. Comment on whether your database meets all the system requirements as specified in Project Specification. Avoid making excuses or comments that reflect negativity. Include an acknowledgement of all students you have spoken to about the assignment.
Video presentation should be submitted to both Moodle A3(3) submission and Google Drive for peer-review. The link of Google Drive will be given through announcement on Moodle.
Peers Review Feedback & Results: Each Video presentation will be reviewed by another two group students through Google Drive. It is required that Each group leader submit the spreadsheet of Peers Review Feedback & Results to Moodle vi A3 (3) submission link.

Part F: Final Report Layout, SQL script and presentation

Deliverables for Parts A, B, C, and D must be printed as a final report with a cover sheet, which includes student's name and student number, unit name, assignment name. See Administrative Details on page 10 for more details. Your final report must include footer that includes page number, report must also be formatted so that it is well set out and easy to read.

Each group must submit a single zip file on Moodle which contains all the assignment files as follows:
Final report, a single word file with deliverables for parts A - D;
The final Crow's Foot Notation ERD save as an editable file .peg/.svg;
The implemented relational database script file for AMC AMCDB.sql that can be used to restore your database and should also be loaded to the PostgreSQL database server through the client psql. You can create the PostgreSQL database script file that contains a list of SQL statements which can be used to restore/recreate the original database.
The testing file .sql, a copy of the whole output of psql for testing Part D, including login to the psql, loading AMCDB.sql into the PostgreSQL database server, showing each table structure (\d tableName;), demonstrating at least 5 records in each tables (select * from tableName;), showing the SQL queries and output for Part D questions.

Reference no: EM133670527

Questions Cloud

What are your thought about different ways to assess student : What are your thoughts about different ways to assess students in the classroom? How did the assignments, videos/readings/materials inform your thinking?

Briefly describe universal design for learning : Briefly describe Universal Design for Learning as it relates to assessment. Make sure to include the three principles of UDL.

Describe pros and cons of eligibility criteria and process : Compare and contrast the two. Describe the pros and cons of the eligibility criteria and process. How might bias impact who is/is not diagnosed?

Create two plus delta charts to describe the positive impact : Create two Plus Delta Charts to describe the Positive and Negative Impact of High Stakes Standardized Testing in Schools and Assessment in Special Education.

Implement a relational database using the structured query : BISY1002 Database Management for Business, Australian Institute of Higher Education - Implement a relational database using the Structured Query Language

Write a mini-literature review on one of the topics : write a mini-literature review on one of the topics from this module: Disability as Identity and Diversity. The disproportionality of students of color.

Describe your experience or exposure to health care finances : Briefly describe your experience or exposure to health care finances. Evaluate how this has this help you in being more conscious of balancing cost and quality?

What visualizing data is and when you would use it : For example "HTML Color Picker". Then note what it is, when you would use it. Next, find a website that uses the method or should use the method.

Explain how technology managers can utilize the nvd : Explain how technology managers can utilize the NVD to enhance security practices. What limitations or challenges did you experience while using the NVD?

User Account

All Pages