Submit a single plain text file with filename as $$studentid$\_$studentname$\_$AL$\_$SQL$$ containing all SQL implementation. Your

SQL queries must work on MS SQL Server and be able to be demonstrated. SQL code required $($Use MS SQL Server$)$:

$1.$ Create a database and CREATE TABLE statements for all tables in your ERD $($Part A$)$ including primary and foreign

keys.

$2.$ INSERT INTO statements for populating the database

i$.$ Insert five rows of $($made$-$up$)$ data into each table. Make sure that the data you enter in these tables should

be sufficient to return at least one row for each query in Task $3.$ AL should hold at least $5$ bottles of Penfold

Grange $2010$ in some branch or other.

$3.$ Select Statements

i$.$ List the branches $($ID$)$ of MA that have in stock at least $5$ bottles of Penfold Grange $2010.$

ii$.$ SELECT statement to generate a list of all email addresses of members whose card will expire in the month

after the coming month. Thus, for instance, if the query is run in November $2121,$ it will list the emails of all

members whose membership will expire in January $2122.$ The emails should be ordered by Branch ID$,$ then

by expiry date, and then by the email address, all in ascending order.

Research and Discussion

Submitted as a MS Word or PDF Document

$4.$ Consider the following relation schema as the join of a few tables from Assignment $1$ ERD

Abnormal$\_$Rel $($ ProductID, BranchID, campaignID, MemberID,

ProductType, PackageType, YearProduced, Price, Brand,

StockLevel, CampaignStartDate, CampaignEndDate, FirstName, LastName, eMail, MembershipLevel,

MemberExpDate, Discount $)$

Determine for UPDATE anomaly whether or not the relation Abnormal$\_$Rel is susceptible to that anomaly. Support

your determination with adequate explanation and a small example.

$5.$ Normalize$/$decompose the relation schema Abnormal$\_$Rel until you get relations that are in $3$NF$.$ Use appropriate

illustration to aid the understanding of your work