Database Assignment: E-R Diagrams, Normalization, and SQL Queries

This assignment covers fundamental database concepts, including constructing Entity-Relationship (E-R) diagrams for recording student marks using both ternary and binary relationships. It discusses the interpretation of disconnected and cyclic graphs in enterprise schema design. The assignment also delves into normalization forms, transforming a customer-artist-painting scenario through unnormalized, 1NF, 2NF, 3NF, and 4NF forms. Practical SQL queries are provided, utilizing the Northwind database for data retrieval operations. Finally, the assignment touches on data mining techniques, specifically the Apriori algorithm for association rule mining and the ID3 algorithm for classification, illustrating their application with sample data and decision tree construction. This comprehensive approach provides a solid foundation in database design, SQL, and data mining principles.

Running head: DATABASE
Database
Name of the Student
Name of the University
Author Note

1
Database
Table of Contents
1. ER diagrams for recording marks.......................................................................................2
2. Enterprise Schema..............................................................................................................3
3. Normalized Forms..............................................................................................................3
4. 4th normalized form.............................................................................................................4
5. SQL.....................................................................................................................................5
6. Northwind Database...........................................................................................................5
7. Apriori Algorithm of Association Rule Mining.....................................................................6

2
Database
1. ER diagrams for recording marks
a) Ternary relationship
b) Binary Relationship
1)

3
Database
2. Enterprise Schema
An E-R diagram can be viewed as a graph. What do the following mean in terms of
the structure of an enterprise schema?
a) Whenever two entities are connected through a specific path while constructing an
ER diagram, it can be said that the entities are related with each other if _IDt
directly, perhaps indirectly. Disconnected graphs means there are entities in the ER
diagram which are unrelated with each other. It implies the entities can be separated
into corresponding database if the graph is split according to the connected
components.
b) As stated in the previous question Whenever two entities are connected through a
specific path while constructing an ER diagram, it can be said that the entities are
related with each other if _IDt directly, perhaps indirectly. If the ER graph contains
cycle that implies the entities contained in the graph are related with each other in at
least two different way. If the graph is acyclic, there is a unique path among every
pair of sets or entity.
3. Normalized Forms
a) Unormalize form
 customer [ customeromer_ID, customer_name, customer_address, customer_phone,
( artist_id, artist_name, art_title, pur_date, price) ]
b) First _IDrmal Form (1NF)
 customer [ customer_ID, customer_name, customer_address, customer_phone]
 customer_art [ customer_ID, art_ID, purchase_date, artist_id, artist_name, art_title,
price ]

4
Database
c) Second _IDrmal Form (2NF)
 customeromer [ customer_ID, customer_name, customer_address, customer_phone]
 customer_art [ customer_ID, art_ID, pur_date, price ]
 art [ art_ID, art_title, artist_id, artist_name ]
d)Third Normal Form (3NF)
 customer [ customer_ID, customer_name, customer_street, customer_city,
customer_prov, customer_pstlcd, customer_phone]
 customer_art [ customer_ID, art_ID, purchase_date, price ]
 art [ art_ID, art_title, artist_id(FK) ]
 artist [ artist_id, artist_name ]
4. 4th normalized form
For a relation to be in $NF certain aspect needs to be considered as for a dependency A → B,
if for a single value of A, multiple value of B exists, then the table may have multi-valued
dependency and for a relation R(A,B,C), if there is a multi-valued dependency between, A
and B, then B and C should be independent of each other.
Example:
Student ID Hobby Course
1 Game Php
1 painting python
4th normalized form

5
Database
Student ID Hobby
1 Game
1 painting
Student ID Course
1 Php
1 python
5. SQL
Using Northwind Database, write SQL statements for the following data retrieval
operations.
a) SELECT employee_name from employee where CITY <> LIKE ‘%le%’;
b) SELECT order_ID FROM order WHERE orderdate BETWEEN '1997-09-25' AND
'1997-12-30';
c) select * from Customers inner join Orders on Customers.CustomerID =
Orders.CustomerID and OrderID = (SELECT TOP 1 subOrders.OrderID FROM
Orders subOrders WHERE subOrders.CustomerID = Orders.CustomerID ORDER
BY subOrders.OrderDate DESC)
6. Northwind Database
Using Northwind Database, write SQL statements for the following data retrieval
operations.

6
Database
a) SELECT * FROM Customer LEFT OUTER JOIN Order ON
Customer.CustomerId = Order.CustomerId WHERE Order.CustomerId IS
NULL
b) 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 INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID;
c) Select customers.*, orders.*
FROM customers RIGHT OUTER JOIN orders ON orders.customer_id =
customers.id;
d) SELECT `first_name`,CONCAT(SUBSTRING(`first_name`,3,1),
UPPER(SUBSTRING(`first_name`,4,2))) FROM employees;
7. Apriori Algorithm of Association Rule Mining.
7.1 Generate the Rules based on following sample data.
c(ABC →D) can be larger or smaller than c(AB →D)
Candidate rule is generated by merging two rules that share the same prefixin the rule
consequent join(CD=>AB, BD=>AC) would produce the candidate rule D => ABC Prune
rule D=>ABC if does not have high confidence
7.2 ID3 Algorithm of Classification Technique
Rote node: Income range

7
Database
Partial decision tree with node: credit card
Partial decision tree with node: age

8
Database
Three node decision tree for the credit card database

9
Database
 IF Age <= 43 and Gender = Male and credit card insurance =NO
THEN life insurance Promotion = NO
 IF Gender = Male and Credit card insurance =NO
THEN Life Insurance Promotion =NO.