Activity-Based Database Management System Assignment

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

MITS4003 –
ACTIVITY SUBMISSIONS
Student name:
Student id:

Contents
Activity-2.........................................................................................................................................4
Task-I E R Diagram.....................................................................................................................4
2.2 ERD for student.................................................................................................................4
Task-2..........................................................................................................................................5
2.4............................................................................................................................................5
Activity-3.........................................................................................................................................6
Task-1 Normalization..................................................................................................................6
3.2............................................................................................................................................6
Task-2 Problems..........................................................................................................................8
3.4............................................................................................................................................8
Activity-4.........................................................................................................................................9
Task-1 Write SQL statements......................................................................................................9
4.4............................................................................................................................................9
4.7............................................................................................................................................9
4.9............................................................................................................................................9
Activity-5.......................................................................................................................................10
5.2..............................................................................................................................................10
5.5..............................................................................................................................................10
5.6..............................................................................................................................................10
5.9..............................................................................................................................................10
Activity-7.......................................................................................................................................11
7.1..............................................................................................................................................11
7.2..............................................................................................................................................14
1

List of Figures
Figure 1: ERD with ternary relationship.........................................................................................4
Figure 2: ERD with binary relationship...........................................................................................5
Figure 3: Final tree.........................................................................................................................16
List of Tables
Table 1: UNF of customer table......................................................................................................6
Table 2: 1NF of customer table.......................................................................................................6
Table 3: 1 NF of customer-artist table.............................................................................................6
Table 4: 2NF of customer table.......................................................................................................7
Table 5: 2NF of customer-artist table..............................................................................................7
Table 6: 2NF of art table..................................................................................................................7
Table 7: 3NF of customer table.......................................................................................................7
Table 8: 3NF of customer-artist table..............................................................................................7
Table 9: 3NF of art table..................................................................................................................8
Table 10: 3NF of artist table............................................................................................................8
2

Activity-2
Task-I E R Diagram
2.2 ERD for student
In ERD, relationships are mainly defined in two forms i.e. binary relationship and ternary
relationship. The term binary and ternary represent the degree in relationship and as the name
indicates, binary relationship involves two entities means two entities have one relationship.
While ternary relationship involves three entities means there is single relationship among three
entities.
(a) ERD with ternary relationship
Figure 1: ERD with ternary relationship
The figure which is shown above represents the ERD diagram of students that enrolled in
different courses and exams and it records the marks. There are three entities namely student,
course, and examination and each having some attribute. All the three entities are linked with
single relationship.
(b) ERD with binary relationship
3

Figure 2: ERD with binary relationship
The figure which is shown above represents the ERD with binary relationship having two entities
which are student and course and both are having common relationship.
Task-2
2.4
(a) In an entity-relationship diagram, if a path is used to connect the sets of entity, then there is
relationship among entity-sets, however there is possibility of indirect relation. Graph is
disconnected means pairs of entity sets are not related with one another and if the graph is
divided into connected components, then there will be distinct database equivalent to each
component that are linked.
(b) As discussed in the above part, if there is path between couple of entity sets, then there exists
relationship between them, possibly indirect. If the graph includes cycle then each couple of the
entity sets are linked with one another and this relationship is in at least two different ways.
4

Activity-3
Task-1 Normalization
3.2
UNF
Table 1: UNF of customer table
Cus_n
o
Cus_na
me
Cus_ad
d
Cus_pho
ne
Artist_i
d
Artist_na
me
Art_titl
e
Pur_date price
01 Jackson 123-4th
Avenu
e
Fonthil
l, ON
L3J
4S4
(206)
284-6783
03 Carol
Channing
Laugh
with
teeth
09/17/20
00
7000.0
0
15 Dennis
Frings
South
toward
emeral
d sea
05/11/20
00
1800.0
0
03 Carol
Channing
At the
movies
02/14/20
02
5550.0
0
15 Dennis
Frings
South
toward
emeral
d sea
07/15/20
03
2200.0
0
1NF
Table 2: 1NF of customer table
Cus_no Cus_name Cus_address Cus_phone
01 Jackson 123-4th Avenue Fonthill, ON L3J 4S4 (206) 284-
6783
Table 3: 1 NF of customer-artist table
Cus_n
o
Art_id Artist_id Artist_name Art_title Pur_date price
01 01 03 Carol
Channing
Laugh with teeth 09/17/2000 7000.00
02 15 Dennis Frings South toward
emerald sea
05/11/2000 1800.00
03 03 Carol At the movies 02/14/2002 5550.00
5

Channing
04 15 Dennis Frings South toward
emerald sea
07/15/2003 2200.00
2NF
Table 4: 2NF of the customer table
Cus_no Cus_name Cus_address Cus_phone
01 Jackson 123-4th Avenue
Fonthill, ON L3J 4S4
[phone]
Table 5: 2NF of customer-artist table
Cust_no Art_id Pur_date Price
01 01 09/17/2000 7000.00
02 05/11/2000 1800.00
03 02/14/2002 5550.00
04 07/15/2003 2200.00
Table 6: 2NF of art table
Art_id Artist_id Artist_name Art_title
01 03 Carol Channing Laugh with teeth
02 15 Dennis Frings South toward emerald sea
03 03 Carol Channing At the movies
04 15 Dennis Frings South toward emerald sea
3NF
Table 7: 3NF of customer table
Cus_no Cus_name Cus_address Cus_phone
01 Jackson 123-4th Avenue Fonthill, ON L3J 4S4 (206) 284-
6783
Table 8: 3NF of customer-artist table
6

Cus_no Art_id Pur_date price
01 01 09/17/2000 7000.00
02 05/11/2000 1800.00
03 02/14/2002 5550.00
04 07/15/2003 2200.00
Table 9: 3NF of art table
Art_id Artist_id Art_title
01 03 Laugh with teeth
02 15 South toward emerald sea
03 03 At the movies
04 15 South toward emerald sea
Table 10: 3NF of artist table
Artist_id Artist_name
01 Carol Channing
02 Dennis Frings
03 Carol Channing
04 Dennis Frings
(Kumar & Azad 2017)
Task-2 Problems
3.4
If there is multivalued dependency in a table, then the relation is difficult to be in 4NF form but
to bring it in 4NF, then it must satisfy the condition that break the table into two separate tables.
7

Activity-4
Task-1 Write SQL statements
4.4 List out employee details, which are not living in city, that have ‘le’ character anywhere
in the name of city.
SELECT * FROM Customers WHERE not city=le
4.7 Display all the orders detail that have orderdate in between '1997-09-25' to '1997-12-
30'.
SELECT orderID, productid, unitPrice, quantity, discount FROM Order Details WHERE
orderdate BETWEEN ‘1997-09-25’ AND ‘1997-12-30’.
4.9 List out information about the latest order for each customer.
SELECT CustomerID, MAX(orderID) FROM orders
8

Activity-5
5.2 Display orders which are placed by customers, use Left Outer Join.
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate,
Orders.requiredDate, Orders.shippedDate, Orders.shipVia, Orders.freight, Orders.shipName,
Orders.shipAddress, Orders.shipCity, Orders.shipRegion, Orders.shipPostalCode,
Orders.shipCountry, Customers.companyName, Customers.Address, Customers.City,
Customers.Region, Customers.postalCode, Customers.Country FROM Customers LEFT
OUTER JOIN Orders ON Customers.customerID = Orders.customerID;
5.5 List the shipper name and the number of orders shipped by each shipper, use Inner
Join.
SELECT Orders.shipName, quantity.Order Details FROM Orders INNER JOIN Order Details
ON Orders.OrderID=Order Details.OrderID
5.6 Display all customers details (who have placed orders) and orders details which are
placed by customers, use Right Outer Join.
SELECT Customers.companyName, Customers.Address, Customers.City, Customers.Region,
Customers.postalCode, Customers.Country, Order Details.orderID, Order Details.productID,
Order Details..unitPrice, Order Details.quantity, Order Details.discount FROM Customers
RIGHT OUTER JOIN Order Detail ON Customers.OrderID=Orders.OrderID
5.9 Retrieve three characters from the employees' first names, starting at the third
character (should be in lower case) and next two characters must be upper case (example:
If first name is “Nancy” then output should be nCY).
SELECT CONCAT(LOWER(SUBSTRING(ContactName 3)),
UPPER(SUBSTRING(ContactName 4,5)) as short name from Customers
9

Activity-7
7.1 Apriori Algorithm of Association Rule Mining
Association rule mining is one of the technique of data mining which mainly includes three
terms:
Support: It defines the total number of particular items in comparison to the record of total items
and is present in percentage form.
Confidence: It is division of the support where item is divided by the condition.
Association Rule: It is basically a rule showing in the form of x=>y means Y will only occur
after the occurrence of x (Yuan 2017).
Given,
Transactions Items
T1 Bread, Jelly, PeanutButter
T2 Bread, PeanutButter
T3 Bread, Milk, PeanutButter
T4 Beer, Bread
T5 Beer, Milk
The total percentage are measured as 100% and since there are total 5 transactions, each
transaction is given 20%.
Item/Itemset Support
Bread 80%
Jelly 20%
10

PeanutButter 60%
Beer 40%
Milk 40%
Bread, Peanut Butter 60%
Associations rules for Apriori Algorithm
Make database set
Items Support
{Bread} 4
{Jelly} 1
{PeanutButter} 3
{Beer} 2
{Milk} 2
Scan the items and calculate how many times an item gets transact
Items Support
{Bread} 4
{Jelly} 1
{PeanutButter} 3
{Beer} 2
{Milk} 2
11