COMP1140 - assignment 2 PDF

Preview

Summary

assignment 2...

Description

COMP1140

01/11/2019 3328287 Joseph Panattu

1. Reflection on your assignment 2 submission: briefly summarise your assignment 2 marker’s comments and suggestions, describe major places you will improve in this submission on assignment 2 content. In the past assignment I had some major issues that the marker pointed out. They pointed out that I have done the EER relationships wrong and that the data dictionary did not meet up with the diagram. There was also small amount of requirement specifications as I could have done more. To improve and fix these issues, I have spent time trying to change and fix my errors, like by fixing the relationships in the EER diagram. I have also put in more requirement specifications. 2. Requirement Specification (including data requirements, transaction requirements and business rules).

Data RequirementsCustomer Details: All the details given about the customers which is being maintained by Admin staf and Staf of Mega Pizza. These details include name (first name and last name), phone number (mobile or/and home phone number), address (house number, street name, suburb/city, state and postcode), and the customer’s order. An ID code is given to each new customer so if they return on a later date, their information is easily accessible. This ID code is applied to the new customer by the admin team or the system. If an existing customer comes in, a new ID code will be set with the updated details. Each customer is sent an email and/or a text message to verify the details given. Accepting this verification will then only save the customer’s details within the database. Order Details: The order is given by the customer to the Staf through either the phone or with in store. If it has been processed through the phone, the customer will ask if it will be collected or delivered. The system will look at the customer’s phone number and look through the database to find the address of the customer, if only the customer chose delivery. If it is a new customer, the steps which are in customer details, will be executed. The order is then processed through the system and then is sent to the cooking employees to make. Staff Details: The Staf’s details are collected when they are hired. These details include, their name (first name and last name), address (street name, suburb/city, state and post code) and the description of the new employee. The position they have at the job as well (whether if it’s in store or delivery) is stored within the database. The Tax file of the number is also taken so it provides a way for Mega Company to pay the new Staf with tax included. The Stafs are also given an ID code to diferentiate between diferent Stafs. A shift is given to each staf. This can be either In-store or delivery driver. If its in-store, they will be allocated somewhere within such as cleaning or cooking. If it is delivery driver, the Staf’s driving and car details will be taken. These include, license number, insurance company and all details about the

car. Each shift is then calculated into the payment by the amount of time they work. For the payment, the staf requires a Tax file number and their bank details. The Bank details of the new Staf is also taken so the salary can be sent somewhere. These details include, the BSB number of the Staf, the bank name they are with and finally account number. Ingredients Details: The ingredients detail also collected. They include, the name of the ingredients, the type of the ingredient (meat, vegetable, etc.), a short description of the ingredient, the current stock level of that ingredient, the price of the Ingredient (both to buy and how much profit Mega Pizza make), the recorder level and the suppliers of the ingredient. The ingredients are also given an ID code to diferentiate between diferent ingredients. Ingredient order information is also taken for ease of access. This includes, the date of the order, the date the order was received, total amount of each order (and of all orders), each orders status. The description of the ingredient is also collected (the condition), the name of the suppliers. The Suppliers’ data is also collected for ease of access, with an ID code given to each supplier. The name of the company/person’s name is also kept, the ingredients they supply, the amount they supply and the total price of both the total amount for each order and also the total of all orders. Menu Details: the data of the menu includes the name of each item (on the menu), the size and also the selling price. Each of the items are given a number so it helps the Stafs and admins and also customers easily order or see the order.

Transactions Requirements Customers: Data Manipulation operations: -

The data collected from the customer can be updated, inserted and also deleted by the employees and admins. This could be if the customer wants to change the order, of if the customer changes his details so they create a new account.

Queries: -

The Employee’s and the Admin’s can search through all the customers to see the details and the order, whether if it was given in store or a phone call. The diference between the Employee’s query and Admin query is Employee’s query can only see any customer which has called or came in the store they work at. Whilst Admins can see all customers of Mega Pizza. They can be searched based on any key word such as name, their ID code, suburb etc.

Employee and order: Data Manipulation operations: -

The Employee’s details can be altered, inserted and deleted just like a customer’s details by the manager of the store and admins. This could be if the employee

-

transfers between stores, leaves or is hired into Mega Pizza. This is including the banking information. The order can be changed, inserted or deleted by the employee’s customers and admins if there is anything wrong with the current order.

Queries: -

The manager and admins can search the details about all the employee’s details to see any information about them. They can be searched based on any key word such as an employer’s name or address.

Ingredients and suppliers: Data Manipulation operations: -

The Employee’s and admins can change, insert or delete any ingredients, whether they introduce a new ingredient or remove one the customers don’t like.

Queries: -

The admins and employees can search all the ingredients by using key words such as the ID code or suppliers.

Business Rule

3.

-

A customer can have many orders, but an order can only have one customer.

-

Their can be more than one employee working on an order.

-

An employee can have many orders

-

Any detail changes within the customers details will cause a new account with the updated customers.

-

A customer can change their details either through calling Mega Pizza or coming in store

-

The manager of the store can delete insert or create customer and employee accounts.

-

An ingredient can have many suppliers

EER Diagram and Data Dictionary

EER Model is on draw.io  Means primary key  Means attribute key Entity Types Entity Name Customer ID

Description A unique ID given to every new customer A unique ID given to every new employee A unique ID given to every new order A unique ID given to every new ingredient A unique number given to every item on the menu A unique ID given to every new supplier A unique number which was assigned to each bank

Employee ID Order ID Ingredients ID Item Number

Supplier ID Bank Number

Aliases Customer, Order

Occurrence Customer

Employee, Order

Employee

Walk In, Phone Order, Order Ingredients, ingredients order Menu

Orders

Supplier, ingredients order Employee’s bank

Ingredients Order, Ingredients Menu

Supplier Employee’s Bank

Relationship Types Entity Name Customer ID Order Type Order Type Employee ID Employee ID Supplier ID

Multiplicity 1..1 0..* 0..* 1..1 1..1 0..*

Relationship Order ID Order ID Order ID Employee ID Account Number Supplier ID

Multiplicity 1..* 1..* 1..* 1..* 1..1 1..*

Ingredients ID Ingredients ID Item Number

1..* 1..* 1..1

Name Item Name Order Type

0..* 0..* 1..1

Entity Name Customers Phone Order Walk in Order Employee Employee’s Bank Ingredients Order Ingredients Menu Order

Attribute Entity Name

Attributes

Description

Order

Customer ID

Order

Employee ID

The ID code of the customer who ordered The ID code of the

Nulls

Data Type & length Char

N

Char

N

Multivalued

Derived

Default

Customer

Phone Number

Walk In Order

Order ID

Phone Order

Order ID

Employee

Tax File Number

Employee’s Bank

BSB Number

Supplier

Name (supplier)

Ingredients Order

Supplier ID

Ingredients Order

Ingredients ID

Ingredients

Name (ingredients)

Menu

Item Name

Employer’s making the order The phone number of the customer The order code for the people who ordered who walked in The order code for the people who ordered on the phone The tax number of the new employee The BSB number of the employee The name of the ingredient’s supplier The unique ID of the supplier of the ingredients The unique ID of the ingredients The Name of the supplier The Name of the item in the Menu

Num

N

Char

N

Char

N

Char

N

Num

N

Char

N

Char

N

Char

N

Char

N

Char

Y

4. Map the EER model to the relational model. Document the relational schema in DBDL. Give normalized relational schema in DBDL. Ensure that normalisation steps are shown.

E

F

Menu

Ingredients

Supplier Customers

D C Order Employee

B A Delivery

In-Store

Worker

Worker

H Order Employee Customers Supplier Ingredients Menu In-store worker Delivery worker

A(Order ID(PK), Order, Employee ID, Customer ID, Quantity) B(Employee ID(PK),First Name, Last Name, Address (Street, City, Suburb),Description, Tax File Number) C(Customer ID(PK),First Name, Last Name, Phone Number, Address (Street, City, Suburb),Order) D(Supplier ID, Name, Ingredients Supplied, Amount Supplied, Total Price) E(Ing Order ID(PK),Name, Type, Description, Stock Level Current, Date of Stocktake, Supplier, Quantity. ) F(Item Number(PK), Item Name, Size, Selling Prize, Quantity.) G(Employee ID(PK), Store ID, Shift) H(Employee ID(PK), Store ID, Shift, Car Details)

G

5. SQL script (both in your sql file and in your report file) which creates the Mega Pizza database as stated in 2.1. -- Employee Table CREATE TABLE Employee ( Employee_id NUMERIC NOT NULL, First_name VARCHAR(1000) NOT NULL, Last_name VARCHAR(900) NOT NULL, Address VARCHAR(1000) NOT NULL, Street VARCHAR(1000) NOT NULL , City VARCHAR(1000) NOT NULL , Suburb VARCHAR(1000) NOT NULL , States VARCHAR(1000) NOT NULL , Post Code NUMERIC(10) NOT NULL , Description VARCHAR(1000) NOT NULL , Tax Files Numbers NUMERIC(20) NOT NULL , CONSTRAINT employees_pk PRIMARY KEY NONCLUSTERED (Employees ID) ); GO -- Customer Table CREATE TABLE Customer ( Customer ID NUMERIC NOT NULL, First_name VARCHAR(1000) NOT NULL, Last_name VARCHAR(900) NOT NULL, Phone Numbers NUMERIC NOT NULL, Address VARCHAR(1000) NOT NULL, Street VARCHAR(1000) NOT NULL , City VARCHAR(1000) NOT NULL , Suburb VARCHAR(1000) NOT NULL , States VARCHAR(1000) NOT NULL , Post Code NUMERIC(10) NOT NULL , Orders VARCHAR(1000) NOT NULL, CONSTRAINT Customers_pk PRIMARY KEY NONCLUSTERED (Customers ID) ); GO

-- Order Table CREATE TABLE Orders ( Orders ID NUMERIC NOT NULL, Orders Types VARCHAR(1000) NOT NULL, Customers ID VARCHAR(900) NOT NULL, Employees ID NUMERIC NOT NULL, Quantity VARCHAR(1000) NOT NULL, CONSTRAINT Order_pk PRIMARY KEY NONCLUSTERED (Orders ID) ); GO -- Ingredients Table CREATE TABLE Ingredients ( Ingredients ID NUMERIC NOT NULL, Name VARCHAR(1000) NOT NULL, Types VARCHAR(900) NOT NULL, Stock Levels Currents VARCHAR(1000) NOT NULL, Dates ofs Stocktake VARCHAR(1000) NOT NULL , Recorder Levels VARCHAR(1000) NOT NULL , Suppliers VARCHAR(1000) NOT NULL , Description VARCHAR(1000) NOT NULL , Quantity NUMERIC (100) NOT NULL , CONSTRAINT Ingredients_pk PRIMARY KEY NONCLUSTERED (Ingredients ID) ); GO

6. SQL statements (both in your sql file and in your report file) satisfying the transaction requirements as stated in 2.2

UPDATE Employees

UPDATE Customers

SET FirstName='Markus',

SET FirstName='John',

LastName='Winand' WHERE EmployeeID=12345;

LastName='May' WHERE Customers ID=123;

exec sp_updatestats;

exec sp_updatestats;

GO

GO

UPDATE Orders

UPDATE Ingredients

SET Orders Types='Apple',

SET Name='Salt',

WHERE Orders ID=123;

WHERE Ingredients ID=123;

exec sp_updatestats;

exec sp_updatestats;

GO

GO...