An online retailer, S record, which sells physical music albums, plans to implement a DBMS to enhance its data management practice and ultimately advance business operations. The requirements collection
and analysis phase of the database design process has provided the following data requirements:
Each album has a unique album ID and the following attributes: album title, label company, album price, stock, release date, and genre. A music album belongs to at least one or more genres. Each genre has a
name and description. An album can be made by an artist or a group of artists. Each artist has a unique artist ID, and they can release multiple albums. Data held on each artist includes artist name and debut
date. The company is also interested in tracking news and magazine articles about the artists. Each article is recorded with attributes such as article ID, date, title, source, and URL. An article has to refer to at least one or more artists.
Each customer must sign up as a member to purchase albums. The membership information includes
name, customer ID, password, address, phone number, birthday, occupation, the default payment method, registration date, and e-mail address. Customers can purchase one or more albums and multiple quantities
for each album. Each order is identified by an order ID. In addition, orders are described by order date, total price, payment method, discount (promotions), and delivery option.
Q1. Draw an ER diagram for the requirements (Explain additional assumptions for your diagram
if necessary).
We do not do your homework for you. Although it might take more effort to do the work on your own, you will profit more from your effort. We will be happy to evaluate your work though.
Cannot diagram on these posts.
yuyutg
To draw an Entity-Relationship (ER) diagram for the given requirements, we need to identify the entities, their attributes, and the relationships between them. Here is a step-by-step guide on how to create an ER diagram for the provided data requirements:
1. Identify the entities:
- Album
- Genre
- Artist
- Article
- Customer
- Order
- Payment Method
- Delivery Option
2. Determine the attributes for each entity:
- Album: album ID (unique), album title, label company, album price, stock, release date
- Genre: genre name, description
- Artist: artist ID (unique), artist name, debut date
- Article: article ID, date, title, source, URL
- Customer: customer ID (unique), name, password, address, phone number, birthday, occupation, registration date, e-mail address
- Order: order ID, order date, total price, discount
- Payment Method: payment method ID (unique), payment method details
- Delivery Option: delivery option ID (unique), delivery option details
3. Identify the relationships between entities:
- An album belongs to one or more genres (one-to-many relationship: Album - Genre)
- An album is made by one or more artists (many-to-many relationship: Artist - Album)
- An article refers to one or more artists (many-to-many relationship: Article - Artist)
- A customer can purchase one or more albums (many-to-many relationship: Customer - Album)
- An order is associated with one customer (one-to-many relationship: Customer - Order)
- An order can have one payment method (one-to-many relationship: Payment Method - Order)
- An order can have one delivery option (one-to-many relationship: Delivery Option - Order)
4. Draw the ER diagram:
```
+------------+ +------------+
| Genre | | Artist |
+------------+ +------------+
| genre_id | | artist_id |
| name | | name |
| description| | debut_date |
+------------+ +------------+
| |
| |
+--------------+--------------+
|
|
+-------v-------+
| Album |
+--------------+
| album_id |
| title |
| label_company|
| album_price |
| stock |
| release_date |
+-------+------+
|
|
+------------+-----------+
| Article |
+------------------------+
| article_id |
| date |
| title |
| source |
| URL |
+-----------------+------+
|
|
+--------v--------+
| Customer |
+----------------+
| customer_id |
| name |
| password |
| address |
| phone_number |
| birthday |
| occupation |
| registration_date |
| email_address |
+-----------+----+
|
|
+------v-------+
| Order |
+--------------+
| order_id |
| order_date |
| total_price |
| discount |
+--------------+
| payment_method_id |
+-------+------+
|
|
+------+------+
| Payment Method |
+----------------+
| payment_method_id |
| method_details |
+-----------------+
| delivery_option_id |
+--------+--------+
|
|
+------v------+
| Delivery Option |
+--------------+
| delivery_option_id |
| option_details |
+-------------------+
```
Additional assumptions:
- Each album can be associated with one or more genres, but it is not specified whether an album must have at least one genre or not.
- Each artist can release multiple albums, but the minimum and maximum number of albums an artist can release is not mentioned.
- An article must refer to at least one artist, but it is not specified whether an article can refer to multiple artists or not.
- Each customer must have a default payment method, but the available payment methods and their details are not mentioned.
- Each order must have a delivery option, but the available delivery options and their details are not mentioned.