Database Systems Assessment 1- (Part B): SQL Implementation and Normalization

ICT503

Database Systems

Assessment 1- (Part B)

Student ID:

Student Name:

Part B: SQL Implementation and Normalization

Task 1: SQL Implementation

The SQL implementation tasks involve the setting up of one or more database tables and then making sure they have content before carrying out one or more particular queries [1].

1. Create Database statements and Create Table statements.

1. Create a Table For the Branch

2. Create a Table for Product

3. Create a table for Staff

4. Create a Table for Member

5. Create a Table for BranchProduct

6. Create a Table for MemberBranch

2. Insert Sample Data

1. Insert Data for Product

2. Insert Data for the Branch

3. Insert Data for BranchProduct

4. Insert Data for Member

3. Select Statements

1. List branches with at least 5 bottles of Penfold Grange 2010

SELECT BranchID

FROM BranchProduct

WHERE ProductID = 1 AND StockLevel >= 5;

This query checks for branches that have ProductID = 1 (Penfold Grange 2010) with a stock level of 5 or more.

2. List emails of members whose membership expires in the month after the coming month:

SELECT Member.Email

FROM Member

WHERE ExpiryDate BETWEEN DATE_ADD(CURDATE(), INTERVAL 2 MONTH) AND DATE_ADD(CURDATE(), INTERVAL 3 MONTH)

ORDER BY ExpiryDate, Email;

This query retrieves members whose membership expires in the month after the next. The emails are ordered by BranchID, ExpiryDate, and Email address.

4. Consider the following 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. If not managed properly, this redundancy leads to a variety of inconsistent information across the database.

Relation Schema:

The provided relation schema is:

Abnormal_Rel(ProductID, BranchID, CampaignID, MemberID, ProductType, PackageType, YearProduced, Price, Brand, StockLevel, CampaignStartDate, CampaignEndDate, FirstName, LastName, Email, MembershipLevel, MemberExpDate, Discount)

Susceptibility to UPDATE Anomaly:

Redundancy in Data: It looks like the schema converged product, campaign, and member details into a single relation. This leads to data redundancy because a member's name, email, or even product details may be repeated for every campaign the member or product is coming across.

UPDATE Anomaly Example: To illustrate, if a member has provided his or her email address in the registration, then it changes, and the system will not be able to send the needed updates or alerts. Since Email is part of this relation the member’s email will need to be updated in every row where that member appears. For example, if a member engages in 10 different campaigns or uses 10 different products his/her email will be recorded ten times. When the update is not performed in any of these rows, there will be inconsistency in the records, the new message will read some rows with old email and new rows with new email.

ProductID	BranchID	CampaignID	MemberID	ProductType	FirstName	LastName	Email
P001	B01	C001	M001	Electronics	John	Doe	john@abc.com
P002	B01	C002	M001	Furniture	John	Doe	john@abc.com
P003	B02	C003	M001	Clothing	John	Doe	john@abc.com

Consequence: Sampling attributes such as FirstName, LastName, and Email as being present in a table used to store products and campaign information is a sign that there is something wrong with the design. About this type of schema where unrelated information (like member details, and product details) are grouped, it should be noted that they cause anomalies.

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 [2]. A relation is in 3NF if:

• It is a Second Normal Form

• It has no influence, or in other words, non-key attributes are not dependent on other non-key attributes.

Step-by-Step Normalization Process:

1. Unnormalized Form (UNF):

The given relation of the name Abnormal_Rel is in UNF format because we have the repeating groups here and it contains information about different entities like members, products & campaigns in the same relation only.

2. First Normal Form

There are no repeating groups (each cell contains atomic values). A primary key is defined. The relation is already atomic (each attribute consists of only one value) so we 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.

3. 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.

Member Table:

Member(MemberID, FirstName, LastName, Email, MembershipLevel, MemberExpDate)

Product Table:

Product(ProductID, ProductType, PackageType, YearProduced, Price, Brand)

The remaining relation will only have the primary keys:

Abnormal_Rel (ProductID, BranchID, CampaignID, MemberID, StockLevel)

4. Third Normal Form (3NF):

If we want to transform it to 3NF we have to remove all transitive dependencies. 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.

Final Tables in 3NF:

Member Table:

MemberID (Primary Key)

FirstName

LastName

Email

MembershipLevel

MemberExpDate

Product Table:

ProductID (Primary Key)

ProductType

PackageType

YearProduced

Price

Brand

Branch Table:

BranchID (Primary Key)

Abnormal_Rel (Associative Table):

ProductID (Foreign Key referencing Product)

BranchID (Foreign Key referencing Branch)

MemberID (Foreign Key referencing Member)

StockLevel

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] AWS, “What is SQL? - Structured Query Language (SQL) ,” Amazon Web Services, Inc., 2022. https://aws.amazon.com/what-is/sql/#:~:text=Structured%20query%20language%20(SQL)%20implementation,queries%20and%20returns%20the%20results. (accessed Oct. 16, 2024).

?

[2] M. Rouse and J. Vaughan, “database normalization,” TechTarget, 2019. https://www.techtarget.com/searchdatamanagement/definition/normalization (accessed Oct. 16, 2024).

?