INFS7007 – SA&DMS
FINAL PROJECT, Weight - 25%
Due: 11:59pm on Friday 16th February (Week 11)
Develop a database system for a Music Store by applying the systems analysis and design methodologies.
In this scenario, we will apply systems analysis and design methodologies to develop a comprehensive database system for a music store. The database system will include entities such as Sales Staff, Manager, Artist, Album, Inventory, Customers, and Orders. We will create an Entity Relationship Diagram (ERD), convert it to a Global Relation Diagram (GRD), and normalise it to 3NF. Additionally, we will insert relevant data into the database system and ensure it can answer various queries.
Phase 1: Planning
In the planning phase, we identify the project requirements, goals, and objectives for the music store database system. We define the scope of the database system and create a project plan, including timelines and resource allocation.
Phase 2: Analysis
During the analysis phase, we gather and analyse information about the music store's operations and data requirements. Key entities identified include Sales Staff, Manager, Artist, Album, Inventory, Customers, and Orders. We define their attributes, relationships, and primary keys based on the business processes.
Phase 3: Design
In the design phase, we create an Entity Relationship Diagram (ERD) that visually represents the entities and their relationships. The ERD will include all necessary details, and we can add more entities, if needed. The ERD will serve as the foundation for our music store database.
Phase 4: Implementation
In the implementation phase, we convert the ERD into a Global Relation Diagram (GRD). The GRD will be normalized to 3NF to ensure data integrity. We will define the tables, their structures, and relationships in the database system. We will also implement security measures and constraints to protect sensitive data.
Phase 5: Data Insertion
To make the system operational, we will insert relevant data into the database system. We will include information about sales staff, managers, artists, albums, inventory, customers, and orders. A maximum of 10 rows of data for each entity will be inserted for demonstration purposes.
Phase 6: Query Capabilities
The music store database system will be capable of answering the following queries:
1. How many albums does this music store offer? What are their names and prices?
2. List all albums, their genres, and prices.
3. How many customers have placed orders for a particular date and time?
4. Who is the artist responsible for a particular album?
5. What is the average age of customers who placed orders for a specific day?
6. List the names of customers and their corresponding orders.
7. List all orders placed for a specific day of the week.
8. List the names of all customers who placed orders for a specific day of the week.
9. List the names of all customers and their favourite artists for each album they ordered.
10. List the names of artists who contributed to a specific genre of music.
11. List the names of Customers whose orders are not confirmed.
12. One query by yourself.
13. One query by yourself.
14. One query by yourself.
By following these phases and implementing the database system with the specified functionalities, the music store will have an efficient and organized system to manage its operations and provide valuable insights for decision-making.