Database Systems Activity: ER Diagrams and Normalization

Desklib provides solved assignments and past papers to help students succeed.

MITS4003- ACTIVITY SUBMISSION

Table of Contents
Activity 2.........................................................................................................................................2
Activity 3.........................................................................................................................................3
Activity 4.........................................................................................................................................4
Activity 5.........................................................................................................................................4
Activity 7.........................................................................................................................................5

Activity 2
Ans 2.2)
a.
Figure 1: ER diagram (2.2.a)
b.
Figure 2ER diagram (2.2.b)
Ans 2.4)
a. The graph is disconnected: In any ER diagram if some entities are connected to each
other by a path then it means that they and their sets are related, which can also be
indirectly. In a disconnected graph there are some entities which are not related to each
other or a graph is disconnected when it has some entities which are not related to each
other at all and by any path.

b. The graph has cycle: If an ER diagram has entities which are connected by a path in
closed chain and they are dependent on each other whether directly or indirectly, then we
can call that ER diagram a graph cycle.
Activity 3
Ans 3.2) In order to normalize this table up to 3rd normal form we have to first normalize it at 1st
and 2nd normal form.
Un -normalize form
customer [ customer_number, customer_name, customer_address, customer_contact,
( artist_id, artist_name, art_title, purchase_date, art_price) ]
First Normal Form (1NF)
customer [ customer_number, customer_name, customer_address, customer_contact]
customer_art[customer_number, art_code, purchase_date, artist_id, artist_name,
art_title, art_price]
Second Normal Form (2NF)
customer [ customer_number, custoner_name, customer_address, customer_contact]
customer_art [ cust_number, art_code, purchase_date, art_price ]
art [ art_code, art_title, artist_id, artist_name ]
Third Normal Form (3NF)
customer [ customer number, customer_name, customer_street, customer_city,
customer_provience, customer_postalcode, customer_contact]
customer art [ cust_number, art_code, purchase_date, art_price ]
art [ art_code, art_title, artist_id(JE) ]
artist [ artist_id, artist_name ]
Ans 3.4) There two for normalisation in 4th Normal Form
a. The given form must be BCNF or Boyce-Codd Normal Form.
b. And the given data table must not have any Multi-valued Dependency.

Activity 4
Ans 4.4) SELECT * from Employees WHERE city like “%le%”;
Ans 4.7) SELECT * from Order WHERE Orders.OrderDate BETWEEN DATETIME('1997-09-
25') And DATETIME('1997-12-30');
Ans 4.9) select CustomerID, max(OrderID) from orders group by CustomerID order by
max(OrderID) desc
Activity 5
Ans 5.2)
SELECT c.customerid,
o.OrderID
FROM customers c
LEFT JOIN orders o ON o.customerid = c.customerid
ORDER BY ordered
Ans 5.5)
select shippers.CompanyName,`order details`.Quantity
from `order details` right join shippers
right join orders on `order details`.OrderID=orders.OrderID
AND shippers.ShipperID=orders.ShipperID;
Ans 5.6)
SELECT c.*, o.*, orderid
FROM customers c
RIGHT JOIN orders o ON o.customerid = c.customerid
ORDER BY orderid
Ans 5.9)
select CONCAT(lower(SUBSTRING(FirstName From 3 For 1)),
UCASE(SUBSTRING(FirstName From 4 FOR 2))) as initials from employees

Activity 7
Ans 7.1)
Transactions Items
T1 Bread, Jelly, PeanutButter
T2 Bread, PeanutButter
T3 Bread, Milk, PeanutButter
T4 Beer, Bread
T5 Beer, Milk
Step 1: In this step, we have to count all the occurrences of each item.
Itemset Count
Bread 4
Jelly 1
PeanutButter 3
Milk 2
Beer 2
Step 2: In this step, we have to calculate the frequency of two items.
Itemset Count
Bread, Jelly 1
Bread, PeanutButter 3
Bread, Milk 1
Bread, Beer 1
Jelly, PeanutButter 1
Milk, PeanutButter 1
Bear, Milk 1
Applying the Apiori Algorithm
{Bread, PeanutButter} association rules,
{Bread}→{PeanutButter}
Support = 3/5 = 0.6
Confidence = 3/4 = 0.75
{PeanutButter}→{Bread}
Support = 3/5 = 0.6
Confidence = 3/3 = 1
{Bread}→{PeanutButter} (s=0.6, c=0.75)
{PeanutButter}→{Bread} (s=0.6, c=1)

Ans 7.2)

Income Range Life Insurance
Promotion
Credit Card
Insurance
Sex Age
40-50K No No Male 42
20-30K No No Male 27
30-40K No No Male 43
20-30K Yes No Male 23
IF Age > 43
THEN life insurance promotion = no
IF age <=43 & sex = female
THEN life insurance promotion = yes
IF age <=43 & sex = male & credit card insurance = no
THEN life insurance promotion = no