COIT20247 Database Design and Development Assignment 2

This report covers tasks involving mapping ERD relations, normalization, functional dependencies, 3rd Normal Form, queries, and implementation in MS-Access. It also discusses learnings and successful completion of complex requirements.

COIT20247 Database Design and Development (T3 2017)
Assignment 2
1

Table of Contents
Introduction......................................................................................................................................4
Normalization..................................................................................................................................4
Mapping the ERD........................................................................................................................4
Functional Dependencies and Demonstration of 3rd Normal Form...........................................11
Functional Dependencies...........................................................................................................11
Demonstration of 3rd Normal Form...........................................................................................12
Queries...........................................................................................................................................14
Report............................................................................................................................................17
Implementation Report..................................................................................................................18
Two things I learned while doing this project...........................................................................18
Complex requirement successfully completed..........................................................................18
Conclusion.....................................................................................................................................19
References......................................................................................................................................20
List of figures:
Figure 1 : Employee entity..............................................................................................................4
Figure 2 :Manager entity..................................................................................................................5
Figure 3: Analyst entity....................................................................................................................6
Figure 4 :Analyst- Recommendation...............................................................................................6
Figure 5 :Manager- Portfolio...........................................................................................................7
Figure 6 :Recommendation- stock...................................................................................................7
Figure 7 :Share registry and Stock...................................................................................................8
Figure 8 :Stock- Trade.....................................................................................................................8
Figure 9:Trade- Portfolio.................................................................................................................9
Figure 10: Portfolio- Client.............................................................................................................9
Figure 11 :Client- Individual.........................................................................................................10
Figure 12 :Client- Trustee..............................................................................................................10
Figure 13 :Client- Corporate..........................................................................................................11
Figure 15:QUERY1.......................................................................................................................15
Figure 16:QUERY2.......................................................................................................................15
Figure 17:QUERY3.......................................................................................................................16
Figure 18: QUERY4......................................................................................................................16
Figure 19-:QUERY5......................................................................................................................17
2

Figure 20:QUERY6.......................................................................................................................17
Figure 21:REPORT.......................................................................................................................18
3

Introduction
The report is based on the tasks which have used the sample solution of the assignment 1. The
different tasks that are covered in the following assessment are mapping relations from the ERD,
normalization of the relations which covers at least the 3rd Normal Form, listing all the functional
dependencies of the relations, creating the relations in the database in MS- Access Database,
applying the CRUD operations on the sample data, creating relationships, enforcing referential
and data integrity, creating queries and then producing reports.
Normalization
Mapping the ERD
1. Employee entity
Figure 1 : Employee entity
Employee (E_ID, First Name, Last Name, Address, Phone, Employee Type)
4

2. Manager entity
Figure 2 :Manager entity
Employee (E_ID, First Name, Last Name, Address, Phone, Employee Type)
Manager (E_ID, ExperienceDes)
3. Analyst entity
5

Figure 3 Analyst entity
Employee (E_ID, First Name, Last Name, Address, Phone, Employee Type)
Analyst (E_ID, Expertise)
4. Analyst- Recommendation
Figure 4 .Analyst- Recommendation
Recommendation (Recommend_id, Recommend_type, reason, date, Made by- E_ID)
Analyst (Expertise,Recommend_id)
6

5. Manager- Portfolio
Figure 5 Manager- Portfolio
Manager (ExperienceDes, Portfolio_id)
Portfolio (Portfolio_id, Available Cash, Managed by- E_ID)
6. Recommendation- stock
Figure 6 Recommendation- stock
Recommendation (Recommend_id, Recommend_type, reason, date, StockCode)
Stock (StockCode, Company name, section, Listed Year)
7. Share registry and Stock
7

Figure 7 Share registry and Stock
Share Registry (Registry name, Homepage URL, StockCode)
Stock (StockCode, Company name, section, Listed Year)
8. Stock- Trade
Figure 8 Stock- Trade
Stock (StockCode, Company name, section, Listed Year)
Trade (Trade_id, quantities, PricePerShare, Trade type, Date, StockCode)
9. Trade- Portfolio
8

Figure 9. Trade- Portfolio
Trade (Trade_id, quantities, PricePerShare, Trade type, Date, Portfolio_id)
Portfolio (Portfolio_id, Available Cash)
10. Portfolio- Client
Figure 10 Portfolio- Client
Client (Client_ID, First Name, Last Name, address, Phone Number, Client type)
Portfolio (Portfolio_id, Available Cash, Owned by-Client_ID)
11. Client- Individual
9

Figure 11 Client- Individual
Client (Client_ID, First Name, Last Name, address, Phone Number, Client type)
Individual (TaxFileNumber, Client_ID)
12. Client- Trustee
Figure 12 Client- Trustee
10

Client (Client_ID, First Name, Last Name, address, Phone Number, Client type)
Trustee (TrustAccount, Client_ID)
13. Client- Corporate
Figure 13 Client- Corporate
Client (Client_ID, First Name, Last Name, address, Phone Number, Client type)
Corporate (ABN, Client_ID)
Functional Dependencies and Demonstration of 3rd Normal Form
Functional Dependencies
These are a kind of relationships that exist in a relation such that if there is a relation, it's one
attribute is uniquely determined by another attribute. For example, if R is any relation and there
are these two attributes, X and Y, if Y is uniquely determined by X, then it is known as a
functional dependency. This can be written as X->Y.
Here we are taking two relations where we have to find out their functional dependencies.
1. Employee Relation
Employee (E_ID, First Name, Last Name, Address, Phone, Employee Type)
The functional dependencies in this relation are:
E_ID-> First Name
E_ID-> Last Name
E_ID-> Address
E_ID-> Phone
E_ID-> Employee Type
11

Here the primary key is EMP_ID and all the other entities are not dependent on each
other. Every attribute has its relation with the E_ID. Therefore, no other dependencies
can occur.
2. Portfolio
Portfolio (Portfolio_id, Available Cash)
The functional dependencies in this relation are:
Portfolio_ID-> Available Cash
Here, there is only one key other than the Primary key or the candidate key. So there is
only a single functional dependency.
Demonstration of 3rd Normal Form
Normalization in the database is used for removing the creation, updating and deletion
anomalies. It ensures the following two things:
1. Redundancy in the database is removed
2. The data dependencies in the database make sense.
1st Normal Form
It states that the data stored in any column must be unique, one and only one. There should be no
two rows that contain repetitive information.
For example,
SNO. NAME DEPARTMENT
1 PETER SCIENCE, ARTS
This table is not in 1st normal form. This should be written as:
SNO NAME DEPARTMENT
1 PETER SCIENCE
2 PETER ARTS
This table is normalized to 1st normal form.
2nd Normal Form
According to this, the relation should be in 1st normal form and also there should not be any
partial dependencies in the relation. It means all the non-prime attributes should be dependent on
all the prime attributes.
12

STUDENT SUBJECT AGE
PETER SCIENCE 12
ADAM MATHS 13
If (Student, subject) is the candidate key, then age only depends on Student and not on Subject.
The table should be re-written as:
STUDENT SUBJECT
PETER SCIENCE
ADAM MATHS
STUDENT AGE
PETER 12
ADAM 13
3rd Normal Form:
For a relation to be in 3rd normal form, it should fulfill the following criteria:
1. It should be in 2nd normal form.
2. There should not be any transitive dependencies in the relation.
STUDENT CITY STATE ZIP DOB
PETER MELBOURNE VICTORIA 3000 2/09/1997
Here City and State depend on zipping. Therefore, there are transitive dependencies. The table
needs to be rewritten as:
STUDENT ZIP DOB
PETER 3000 2/09/1997
ZIP CITY STATE
3000 MELBOURNE VICTORIA
Here we are taking two relations and determining that both of them are in 3rd normal form.
1. Employee (E_ID, First Name, Last Name, Address, Phone, Employee Type)
1st normal form
Here E_ID is the only primary key. There can only be one employee id for every
employee. The first name and the last name also can be only one for everyone. The
address can be of different types, i.e., home and work addresses but our study covers only
one address. Therefore, no multiple values can be stored. Phone numbers can also be
different but our study only covers one, therefore no multiple values can be stored in this
13

column. The employee can only be of a single type. Therefore, each column holds a
unique and single value. Therefore, this relation satisfies 1st normal form.
2nd normal form
Here, E_ID is the only key which uniquely identifies the row. There are no other unique
fields. Therefore, E_ID is a single-valued candidate key. All the other fields depend on a
single key as a whole. Therefore, there are no partial dependencies. Thus, the relation
satisfies 2nd normal form.
3rd normal form
Here, there are no such non-prime attributes which depend on each other. First name and
Last name do not depend on any other non- prime attribute. Even the address and phone
number neither depend on each other or any other key except the primary key. The
employee type also depends only on the primary key. Therefore, there are no transitive
dependencies. Thus, the relation is in 3rd Normal Form.
2. Portfolio (Portfolio_id, Available Cash)
1st normal form
Here, there only one primary key Portfolio_id which uniquely identifies the table.
Therefore, it holds only one value for every portfolio. The Available Cash can also be a
single value. Therefore, the columns hold unique and single values. Thus, it is in 1st
normal form.
2nd normal form
Here, portfolio_id is the single-valued candidate key as well as the primary key. There
are no columns other than Available cash which depends only on the primary key. Thus,
there are no partial dependencies. Thus, the relation is in 2nd normal form.
3rd normal form
Here, there is only one non-prime attribute. Thus, there can be no transitive
dependencies. Thus, the relation is in 3rd normal form.
Queries
1) QUERY1: SELECT Stock.Stock_code, Stock.Company_name, Stock.Section,
Stock.Listed_year
FROM Stock INNER JOIN recommendation ON
Stock.Stock_id<>recommendation.Stock_id;
14

Figure 14-QUERY1
2) QUERY2: SELECT Analyst.Analyst_first_name, Analyst.Analyst_last_name, Count(*)
AS Expr1
FROM Analyst INNER JOIN recommendation ON (Analyst.E_ID =
recommendation.E_ID) AND (Analyst.E_ID = recommendation.E_ID)
GROUP BY Analyst.Analyst_first_name, Analyst.Analyst_last_name, Analyst.E_ID;
Figure 15-QUERY2
3) QUERY3: SELECT Stock.Stock_code, Stock.Company_name, Stock.Section,
Stock.Listed_year
FROM Trade INNER JOIN Stock ON Trade.Stock_id=Stock.Stock_id
15

GROUP BY Stock.Stock_code, Stock.Company_name, Stock.Section, Stock.Listed_year
HAVING Count(Trade.Stock_id)>2;
Figure 16-QUERY3
4) QUERY4: SELECT Stock.Stock_code, Trade.Quantities, Trade.PricePerShare,
Trade.Trade_date
FROM Trade INNER JOIN Stock ON Trade.Stock_id=Stock.Stock_id
GROUP BY Stock.Stock_code, Trade.Quantities, Trade.PricePerShare, Trade.Trade_date
HAVING Trade.Trade_date>=DateAdd("m",-6,Date());
Figure 17- QUERY4
5) QUERY5: SELECT Analyst.Analyst_first_name, Analyst.Analyst_last_name,
recommendation.Recommend_type, recommendation.reason,
recommendation.Recommend_date
FROM Stock INNER JOIN (Analyst INNER JOIN recommendation ON (Analyst.E_ID
= recommendation.E_ID) AND (Analyst.E_ID = recommendation.E_ID)) ON
Stock.Stock_id = recommendation.Stock_id
WHERE recommendation.Recommend_date<#2017-07-01#;
16

Figure 18- QUERY5
6) QUERY6: SELECT Manager.Manager_first_name, Manager.Manager_last_name,
Trade.PricePerShare
FROM Stock INNER JOIN ((Manager INNER JOIN Portfolio ON Manager.E_ID =
Portfolio.E_ID) INNER JOIN Trade ON Portfolio.Portfolio_id = Trade.Portfolio_id) ON
Stock.Stock_id = Trade.Stock_id
WHERE Stock.Company_name="Commonwealth Bank of Australia";
Figure 19-QUERY6
Report
Query of Report: SELECT Client.First_name, Client.Last_name, Stock.Stock_code,
Trade.Quantities, Trade.Trade_date
FROM Stock INNER JOIN (((Client INNER JOIN Corporate ON Client.Client_id =
Corporate.Client_id) INNER JOIN Portfolio ON Client.Client_id = Portfolio.Client_id) INNER
JOIN Trade ON Portfolio.Portfolio_id = Trade.Portfolio_id) ON Stock.Stock_id =
Trade.Stock_id;
17

Figure 20- REPORT
Implementation Report
Two things I learned while doing this project
1. I learned about the procedural implementations of the queries. I learned that how the data
from the different tables in the database is mined to extract some useful information and
to keep records of the happenings in the company. I learned that how better analysis can
be done of the data available.
2. The second thing I learned from this assignment was how the repetitive data in the tables
can be avoided through dependencies and normalization.
Complex requirement successfully completed
The most complex requirement was joining of the tables and performing inner join operations
with Group By and Having aggregate functions. But, I analyzed the whole database and jotted
down manually the relationships. After writing partial queries and then combining them by
joining, I implemented the queries. It provided lots of information as to how the data from the
tables is mined to suit our purpose.
18

Conclusion
This assessment has covered the topics such as mapping of ERD into relations which involved
one to one cardinality, one to many mandatory, one to many optional, many to one and many to
many cardinalities. It also covered the relation of the strong and weak entity set. Next, the
assignment covered normalization of the relations and mapping of the functional dependencies in
a relation. The relational database included the creation of tables, forms, implementation of
queries and report designing.
Basically, it covered the database designing and development along with the fetching of data and
showing it to the end-users.
19

References
1) Study Tonight 2017, Database Technology, viewed 23 January
2018,https://www.studytonight.com/dbms/database-normalization
2) Svoboda, M 2015, Functional Dependencies, and Keys, viewed 23 January
2018,http://www.ksi.mff.cuni.cz/~svoboda/courses/2015-1-A7B36DBS/practices/
Practice-06-FD.pdf
3) Rusell, G 2016, SQL – Joins and Views, vol. 3.0, viewed 24 January 2018,
https://db.grussell.org/slides/sql3.pdf
4) Dofactory 2018, SQL Having Clause, viewed 24 January 2018,
http://www.dofactory.com/sql/having
5) Browne A 2009, Validation Rules, viewed 24 January 2018,
http://allenbrowne.com/ValidationRule.html
20

21