ICT503 – Database Systems Assessment 1- (Part B)

ICT503 – Database Systems

Assessment 1- (Part B)

Student ID:

Student Name:

List of Figures

List of Table

Part B

Task 1

Create database Statement and Create Table Statement

Create database Liquor;

use Liquor;

Figure 1: Liquor Database

Create Tables

Table 1:

Branch Table

Figure 2: Branch Table

Table 2

Product Table

Figure 3: Product Table

Table 3

Staff Table

Figure 4: Staff Table

Table 4:

Membership Table

Figure 5: Membership Table

Table 5

Supervisor Table

Figure 6: Supervisor Table

Table 6:

BranchProduct Table

Figure 7: Branch Product Table

Table 7:

Member Branch Table

Figure 8: Member Branch Table

Task 2

Insert Sample Data

Insert Data for Product

Figure 9: Product Data

Insert Data for Branch

Figure 10: Branch Data

Insert Data for BranchProduct

Figure 11: Branch Product Data

Insert Data for MemberShip

Figure 12: Membership Data

Task 3

1. List the branches (ID) of MA that have in stock at least 5 bottles of Penfold Grang 2010

SELECT BranchID

FROM BranchProduct

INNER JOIN Product ON BranchProduct.ProductID = Product.ProductID

WHERE Product.Brand = 'Penfold Grange 2010' AND BranchProduct.StockLevel >= 5;

Figure 13:BranchID

2. SELECT statement to generate a list of all email addresses of member.

SELECT MemberShip.Email

FROM MemberShip

WHERE ExpiryDate BETWEEN

DATE_ADD(CURDATE(), INTERVAL 2 MONTH)

AND LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 2 MONTH))

ORDER BY ExpiryDate ASC, Email ASC;

Figure 14: Member Email

=

Task 4

Relation schema as the join of a few tables

If the relation Abnormal_Rel is to UPDATE anomalies one must first understand that an UPDATE anomaly is. What is special about an UPDATE anomaly is that redundancy exists in a database and modifying data at one place comes with the need to update the same data in other places to make the context around the data consistent [1].

Relation Schema: Abnormal_Rel

Table 1: Abnormal Table

Attribute	Meaning
ProductID	Unique identifier for a product
BranchID	Unique identifier for a branch
CampaignID	Identifier for a marketing campaign
MemberID	Identifier for a member
ProductType	Type of product (e.g., Beer, Wine)
PackageType	Packaging information (e.g., Bottle)
YearProduced	Year the product was produced
Price	Retail price of the product
Brand	Brand name (e.g., Tooheys)
StockLevel	Number of items in stock
CampaignStartDate	Start date of the marketing campaign
CampaignEndDate	End date of the marketing campaign
FirstName	Member’s first name
LastName	Member’s last name
Email	Member’s contact email
MembershipLevel	Membership level (Platinum, Gold, etc.)
MemberExpDate	Membership expiration date
Discount	Discount percentage for the member

The Abnormal_Rel table contains product, branch, campaign, and member data in a single relation.

Task 5

Normalization of Abnormal_Rel to 3NF

The next step is to split the relation Abnormal_Rel into relations that are all in the Third Normal Form (3NF). Normalization is the method that helps to categorize data in such a way that input data will be more consistent to increase the rates of constancy. A relation is in 3NF if:

• Second Normal Form

Step-by-Step Normalization Process:

1. First Normal Form

There are no repeating groups. A primary key is defined. The relation is already atomic (each attribute consists of only one value) so name the primary key. Here, the fields ProductID, BranchID, CampaignID, and MemberID can also be used as primary keys since none of the records will be similar if all these parameters are filled

2. Second Normal Form

To convert the relation to 2NF, it must be certain of the fact that all of the non-key attributes are dependent on the primary key as a whole.

Decomposition into 2NF Tables

Product Table:

ProductID	ProductType	PackageType	YearProduced	Price	Brand
1	Beer	Can	2022	25.00	Tooheys New
2	Wine	Bottle	2019	150.00	Penfold Grange 2010

Branch Table:

BranchID	StockLevel
1	10
2	20

Campaign Table:

CampaignID	CampaignStartDate	CampaignEndDate
1	2024-10-01	2024-12-31

Member Table:

MemberID	FirstName	LastName	Email	MembershipLevel	MemberExpDate	Discount
1	Alice	Johnson	alice@example.com	Platinum	2024-12-31	10%
2	Bob	Brown	bob@example.com	Gold	2025-01-15	5%

3. Third Normal Form (3NF):

A transitive dependency arises when a non-key attribute depends on another non-key attribute. Transmission of dependencies is not a problem in the given tables because the non-key attributes are dependent only on the primary key of that particular table.

Decomposition into 3NF Tables

Membership Table:

MemberID	FirstName	LastName	Email	MembershipLevel	MemberExpDate
1	Alice	Johnson	alice@example.com	Platinum	2024-12-31
2	Bob	Brown	bob@example.com	Gold	2025-01-15

Discount Table:

MembershipLevel	Discount
Platinum	10%
Gold	5%

Final Normalized Tables

Product Table:

ProductID

ProductType

PackageType

YearProduced

Price

Brand

Branch Table

BranchID

StockLevel

Campaign Table

CampaignID

CampaignStartDate

CampaignEndDate

Member Table

MemberID

FirstName

LastName

Email

MembershipLevel

MemberExpDate

Discount Table:

MembershipLevel

Discount

Conclusion

In this report, a step-by-step procedure regarding the construction of the ER diagram, physical database design, the use of SQL, and data normalization has been specified. By following these steps the A-One Liquor database system will be efficient, easy to scale, and free from data anomalies.

References

[1] Simplilearn, “Comprehensive Guide to Data Anomalies,” Simplilearn.com, Jul. 11, 2024. https://www.simplilearn.com/data-anomalies-article (accessed Oct. 16, 2024).

?