Reference no: EM132534571
NIT5130 Database Analysis and Design - Victoria University
Scenario for assignment
In this assignment, students are required to develop an inventory control system. Consider this scenario. Melbourne Convenient Store (MCS) required a Database Management System to enable convenient stores to manage their day-to-day operations. Each salesperson in the store has staff identification number, name, and contact details. The MCS records each product as product code, Product name, and Product price.
The proposed MCS Database System should also maintain consumer's information by registering their name, address, email, and phone number. The store also records invoice number and issue date before sending an invoice to a consumer. Items of each invoice which describe the products bought by the consumer should be recorded. Additionally, the system should record the quantity of each product. This whole process can be achieved by creating a database using WebMatrix and WordPress and making use of supporting features like forms, queries, and reports. Note: Database System must be able to perform the following functions:
• Store Product
• Delete Product
• Update Product
• Manage Shopping Cart
Part Task 1: Database Modelling
Develop an Entity-Relationship (ER) model to characterize the information requirements of business illustrates in the case study mentioned above. The diagram of your ER model must
A. Demonstrate all necessary entities
B. Demonstrate all attributes
C. The relationships between entities
D. Demonstrate unique identifiers and nature of interaction among entities
Discovered business rules for your ERD
Map the Entity Relationship Diagram (ERD) into a set of relations in at least Third Normal Form (3NF). You should show that each relation satisfies the rules for 1NF, 2NF, and 3NF.
For example STUDENT (StudentID, FirstName, SecondName, Phone). This relation is in 1NF as it does not contain any repeating groups and show primarykey.
Data dictionary:
|
Attributes
|
Meaning
|
Data Type
|
Other
|
|
Student No.
|
A unique identifier
of Students
|
Integer(8)
|
Index - Yes
(No Duplicate)
|
|
Given Name
|
First name of
students
|
Text(20)
|
Example,
"Reuben"
|
|
Family Name
|
Family name of
students
|
Text(20)
|
Example,
"Steel"
|
|
Phone No.
|
Phone no. of
students
|
Integer(12)
|
Not Null
|
|
Street No.
|
Street no. of students
|
Integer(6)
|
Not Null
|
|
City
|
City where students
live
|
Text(10)
|
Example,
"Melbourne"
|
|
State
|
State where student belong
|
Integer(6)
|
Example, "Victoria"
|
|
Post code
|
Postcode of students
|
Integer(8)
|
Not Null
|
|
Job Type
|
Job type of students
|
Text(15)
|
Example,
"Part -Time"
|
|
Positions
|
Positions of Students
|
Text(20)
|
Example,
"Mentoring"
|
|
Roaster
|
Schedule of work
|
Date & Time
|
DD/MM/YYYY
|
|
Facility Type
|
Type of facility
|
Text(20)
|
Example,
"pool-table"
|
Your report must also include a data dictionary. You should briefly describe all entities and attributes, descriptions are clear, reasonable field formats, etc. For example,
Task 2: Database Implementation
A. Implement your data dictionary to create a relational database in PhPMyAdmin.
B. Populate the database with sample data. Adequate sample data must be included to show that the queries, view(s), and report(s) work properly
C. Build the following forms and reports, requiring a separate query to assure every of the following information needs: e.g.
1. Product information entry form
2. A form to allow the store manager to upload new product and delete the product
3. A form to allow the store manager to change product details and update the product
4. A report for the manager to see store inventory
Attachment:- Database Analysis and Design.rar