Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

T1 2020 ISY1002 ISY103 DBM Group Project Outline.pdf Unit ISY1002/ISY103 Database Management for Business Assessment Type Group Assignment Database Project, Report and Individual Video Presentation...

1 answer below »
T1 2020 ISY1002 ISY103 DBM Group Project Outline.pdf




Unit

ISY1002/ISY103 Database Management for Business

Assessment
Type
Group Assignment Database Project, Report and Individual Video Presentation
Assessment
Numbe
3
Assessment
Weighting
40%
Total Marks

100
Alignment with
Unit and Course

Unit Learning Outcome
ULO2: Show an understanding of the fundamentals
of database design, implementation, management,
and maintenance, including consideration of
appropriate data security
ULO3: Implement a relational database using the
Structured Query Language (SQL) Data Definition
Language (DDL)
ULO4: Demonstrate an ability to query a relational
database using Structured Query Language (SQL) and
Data Manipulation Language (DML)
Graduate Attributes
Assessed
GA1:Communication
GA2: Collaboration
GA4: Critical Thinking &
Problem Solving
GA5: Flexibility
Due Date/Time

End of Week 8 10 & 11 refer Moodle for date and
time.




























1 | P a g e
2 | P a g e
Aim:
To give you practical experience in database modelling, normalization and writing SQL
statements to query a relational database

Assignment Summary:

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

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

Students require to submit 5 minutes individual video presentation, incuding slides to
Moodle.

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 cu
ent 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, subu
,
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
ecords 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
ecorded 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 characterised by product number, name,
and, 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 characterised 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.
3 | P a g e
The proprietors of AMC have appointed your group as Database System Consultant to analyse,
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 centralise 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
e required to enter a representative sample data into your tables in order to test the design
and operation of your database.


Project Specification:

1. Part A: Entity Relationship Diagram (20 marks)

1. Your first task is to study the Case study requirements and gather as much
knowledge to list all the main Entities and attributes.

2. Create an Entity Relationship Diagram (ERD) to help you decide on the
elationships.

Your entity relation diagram that models your database design should:

i. Include all entities, relationships (including names) and attributes.

ii. Identify primary and foreign keys.

iii. Include cardinality/ multiplicity and show using cro s feet or UML
notation.
iv. Include participation (optional / mandatory) symbols if applicable.

The ERD should be created as part of a Microsoft Word document. Hand-drawn
diagrams will not be accepted. It is recommended that you complete your ERD
using Draw.io, Visio or try www.gliffy.com (Search for ERD gliffy to get started).


2. Part B: Normalisation (5 marks)

1. Normalise all entities and relationships in optimal normal form. Provide the
normalised schema and discuss the level of normalisation achieved for each
elation. You goal should be normalise all entities in 3NF. Provide the reasons for
any relation that is not maintained in 3NF.


3. Part C: Relational schema and PostgreSQL database (20 marks)



1. Create relational schema that matches ERD and normalised 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. (5 Marks)
4 | P a g e
2. Using PostgreSQL, you are required to develop a demonstration prototype
system (database) that stores data for AMC stores, employees, products,
suppliers, patrons and orders. Create a new database called AMCDB. Create
tables according to your relational schema created in step XXXXXXXXXXMarks)

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
underscore_case or use camelCase to separate parts of a name.

You are required to be consistent in tabl name, fiel name, data types, field
size and key constraints with relational schema created in step 2.

3. Add at least three records into each table. (5 Marks)


4. Part D: SQL (24 marks)



Use the AMCDB database that you created in PostgreSQL to design and execute
SQL queries that answer the following questions.
Number your answers to each question clearly. The answer to each question must
e 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:
SQL: SELECT lName, position
FROM Staff
WHERE salary > 20000;
Output: +-------+----------+
| lName | position |
+-------+----------+
| Brand | Manager |
| White | Manager |
+-------+----------+
2 rows in set (0.03 sec)
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 Word document 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 co
ect.


Format both the SQL and also the Output in Courier New
10 or 11 point.
5 | P a g e
1. 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. (2 marks)
2. List all the products having price less than $ XXXXXXXXXXmarks)
3. List the product number, product title, quantity ordered, unit price and total amount per
product (unit price * quantity ordered) for order having order number XXXXXXXXXXmarks)
4. 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 , Geo etc. in
street should be returned by your query too. (2 marks)
5. List all the
Answered Same Day May 29, 2021 ISY1002

Solution

Neha answered on May 31 2021
134 Votes
59253/~$Report.docx
59253/ER Diagram.png
59253/Presentation.pptx
Submitted By:-
AMC Database
Entity Relationship Diagram
Normalization
It is important to normalize all the tables in a database as it helps to organise the data available in the database using a predefined format. The normalization helps in insertion, updating and deletion in the tables without any anomalies. It removes the redundancy. The values are stored in rows and columns and also removes the replicated values.
First normal form
    Every column has atomic value and unique name.
Second normal form
    To get the database in second normal form it is important to     remove all the partial dependencies from it. When a non-attribute     key has dependency on some part of a candidate key then it is     known as partial dependency.
Third Normal Form
    There should be no transitive dependency in the database. It helps     in removing the data redundancy and data integrity.
Relational Schema
Patron (PatronId, FirstName, LastName, Address, Phone)
Shop (ShopId, Name, PhoneNo, Email, Fax, Street, Subu
, State, Postcode)
Supplier (SupplierId, Name, Address,Phone,Email)
Product (ProductId, Name, Brand, Description, Price, SupplierId)
Inventory (InventoryId, ShopId, ProductId, Available, Ordered)
Department (DepartmentId, Name, ShopId, Email, Phone)
Employee (EmployeeId, EmpName, PhoneNo, Email, Address, TFN, Salary, JoiningDate, DepartmentId, EmployType)
OrderDetail (OrderId, Date, ProductId, Quantity, PatronId)
PaySlip (PayslipId, ShopId, hours, employeeId, grosspay
What I learnt
While working on this report, I learnt about the different requirements of normalization and its importance while designing the database. There are 9 tables in this database which are related with each other. Every table has a primary key which is used by other tables. I tried to eliminate the redundancy and data integrity from the database and all the tables satisfy the requirements of third normal form.
Thank You
59253/Report.docx
Part A: ERD for AMC
Part B: Normalisation
It is important to normalize all the tables in a database as it helps to organise the data available in the database using a predefined format. The normalization helps in insertion, updating and deletion in the tables without any anomalies. It removes the redundancy. The values are stored in rows and columns and also removes the replicated values.
First normal form
Every column has atomic value and unique name.
Second normal form
To get the database in second normal form it is important to remove all the partial dependencies from it. When a non-attribute key has dependency on some part of a candidate key then it is known as partial dependency.
Third Normal Form
There should be no transitive dependency in the database. It helps in removing the data redundancy and data integrity.
Part C: Relational Schema
Patron (PatronId, FirstName, LastName, Address, Phone)
Shop (ShopId, Name, PhoneNo, Email, Fax, Street, Subu
, State, Postcode)
Supplier (SupplierId, Name, Address,Phone,Email)
Product (ProductId, Name, Brand, Description, Price, SupplierId)
Inventory (InventoryId, ShopId, ProductId, Available, Ordered)
Department (DepartmentId, Name, ShopId, Email, Phone)
Employee (EmployeeId, EmpName, PhoneNo, Email, Address, TFN, Salary, JoiningDate, DepartmentId, EmployType)
OrderDetail (OrderId, Date, ProductId, Quantity, PatronId)
PaySlip (PayslipId, ShopId, hours, employeeId, grosspay)
Table Patron
        Column Name
        Data Type
        Length
        Key Constraints
        PatronId
        Bigint
        
        Primary
        firstname
        varcha
        50
        
        lastName
        varcha
        50
        
        Phone
        varcha
        20
        
        Address
        varcha
        100
        
Table Shop
        Column Name
        Data Type
        Length
        Key Constraints
        ShopId
        Bigint
        
        Primary
        Name
        varcha
        50
        
        PhoneNo
        Varcha
        50
        
        Email
        Varcha
        50
        
        Fax
        varcha
        50
        
        Street
        Varcha
        30
        
        Subu
        Varcha
        30
        
        State
        Varcha
        30
        
        Postcode
        int
        
        
Table Supplie
        Column Name
        Data Type
        Length
        Key Constraints
        SupplierId
        Bigint
        
        Primary
        Name
        varcha
        50
        
        Address
        varcha
        100
        
        Phone
        varcha
        20
        
Table Product
        Column Name
        Data Type
        Length
        Key Constraints
        ProductId
        Bigint
        
        Primary
        Name
        varcha
        50
        
        Brand
        varcha
        50
        
        Description
        varcha
        100
        
        Price
        int
        
        
        SupplierId
        Int
        
        Foreign
Table Inventory
        Column Name
        Data Type
        Length
        Key Constraints
        InventoryId
        Bigint
        
        Primary
        ShopId    
        Bigint
        
        Foreign Key
        ProductId
        Bigint
        
        Foreign Key
        Available
        int
        
        
        Ordered
        int
        
        
Table Department
        Column Name    
        Data Type
        Length
        Key Constraints
        DepartmentId
        Bigint
        
        Primary
        Name
        varcha
        50
        
        ShopId
        Bigint
        
        Foreign Key
        Email
        Varcha
        50
        
        PhoneNo
        Varcha
        50
        
Table Employee
        Column Name
        Data Type
        Length
        Key...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here