|
Relation |
Attribute |
Key |
|
Employee |
EmployeeID |
PK |
|
FirstName |
||
|
lastName |
||
|
address |
||
|
phone |
||
|
Manager |
EmployeeID |
PK,FK Foreign key (EmployeeID) references Employee (EmployeeID) |
|
Experience |
||
|
Analyst |
EmployeeID |
PK,FK Foreign key (EmployeeID) references Employee (EmployeeID) |
|
expertise |
||
|
Trade |
TradeID |
PK |
|
quantity |
||
|
pricePerShare |
||
|
TradeType |
||
|
Date |
||
|
PortfolioID |
FK Foreign key (portifolioID) references portfolio (portifolioID) |
|
|
Portfolio |
PortfolioID |
PK |
|
availableCash |
||
|
clientID |
FK Foreign key (ClientID) references Client (ClientID) |
|
|
employeeID |
FK Foreign key (EmployeeID) references Analyst (EmployeeID) |
|
|
Client |
ClientID |
PK |
|
firstName |
||
|
lastName |
||
|
Address |
||
|
Phone |
||
|
ClientType |
||
|
Individual |
ClientID |
PK,FK Foreign key (ClientID) references Client (ClientID) |
|
TaxFileNumber |
||
|
Trustee |
ClientID |
PK,FK Foreign key (ClientID) references Client (ClientID) |
|
TrustAccount |
||
|
Corporate |
ClientID |
PK,FK Foreign key (ClientID) references Client (ClientID) |
|
ABN |
||
|
Stock |
StockCode |
PK |
|
CompanyName |
||
|
Section |
||
|
listedYear |
||
|
RegistryName |
FK Foreign Key (RegistryName) References Registry (RegistryName) |
|
|
TradeID |
FK Foreign key (TradeID) References Trade (TradeID) |
|
|
Recommendation |
RecommendID |
|
|
RecommendType |
||
|
Reason |
||
|
Date |
||
|
EmployeeID |
FK Foreign Key (EmployeeID) References Analyst (EmployeeID) |
|
|
StockID |
FK Foreign Key (StockID) References Stock (StockID) |
|
|
ShareRegistry |
RegistryName |
PK |
|
HomePageURL |
b) Functional dependencies and demonstration of 3NF.
The relations chosen to show functional dependencies and demonstrate relations meet 3NF are;
- Employee Relation
Functional Dependencies
Figure 1: employee functional dependencies
Figure 1 above shows the dependencies existing in the employee relation. The relation has a full functional dependency because all the other attributes depend and are determined by the EmployeeID which is the key attribute of the relation.
Third Normal Form
- The relation is in 1NF because a primary key EmployeeId has been identified as the key attribute and there are no repeating groups. There are no repeating groups because for every employee identified by a unique EmployeeID the employee has only one firstname, one lastname, one address, one phonenumber. Thus the relation qualifies to be in 1NF.
- The relation is 2NF because there exists a primary key which is EmployeeID. EmployeeID is used to functionally determine every other attribute in the employee relation. Two or more employees can have the same firstname or lastname thus making EmployeeID the only candidate key as there are no other partial dependencies possible within the relation. Thus, the relation is in 2NF.
- The relation is in 3NF because there are no transitive dependencies in the relation. No other attribute except employeeID which is the key attribute can be used to determine any another attribute in the relation. Thus the employee relation is in 3NF because it meets all requirements for 1NF, 2NF and 3NF.
- Client Relation
Functional Dependencies
Figure 2: Client relation functional dependencies
Figure 2 above shows the dependencies existing in the client relation. The relation has a full functional dependency because all the other attributes depend and are determined by the ClientID which is the key attribute of the relation.
Third Normal Form
- The relation is in 1NF because a primary key ClientID has been identified as the primary key and there are no repeating groups. There are no repeating groups because for every client identified by a unique ClientID the client has only one firstname and one of every other attribute in the relation. Thus the relation qualifies to be in 1NF.
- The relation is 2NF because it has a primary key clientID which is used to functionally determine every other attribute in the employee relation. Two or more clients can have the same firstname or lastname or be of the same type thus this makes ClientID the only candidate key as there are no other partial dependencies possible within the relation. Thus, the relation is in 2NF.
- The relation is in 3NF because there are no transitive dependencies in the relation. No other attribute except clientID which is the key attribute can be used to determine any another attribute in the relation. Thus the client relation is in 3NF because it meets all requirements for 1NF, 2NF and 3NF.
3 Implementation report
While undertaking the whole task, I encountered a few problems which turned out to be good learning points for me as I tried to figure out how to go around them to achieve the specified requirement as per the requirements document. The most challenging thing about the task was writing the queries. The query part of the assignment was a bot technical as it involved doing a lot of joins in order to achieve the correct results.
As a result of undertaking the task I learnt about relationships and data integrity and how data integrity is enforced during population of a table with data. A table i.e. child table referencing another table i.e. parent table cannot be populated with data if no record of that data exists in the parent table. For example the relationship between the employee table and the analyst table. To insert a record in the analyst table a record of that employee should exist in the employees table. The relationships are very useful when doing joins using a more than two tables.
Watt, A. (n.d.). Database Design:Chapter 11 Functional Dependencies. Retrieved April 22, 2017, from https://opentextbc.ca/dbdesign/chapter/chapter-11-functional-dependencies/
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
