Relational database implementation
You must implement the entity relationship model provided in the sample solution of Assignment.
In implementing the ERD provided, you must complete the following tasks:
• Map the ERD into a set of relations in at least Third Normal Form (3NF). You must ensure that your relations meet 3NF. There is no need to show your working.
• Select any two (2) of your relations from the previous step and for each of them:
• List all the functional dependencies present in the relation
• Demonstrate that the relation meets Third Normal Form (3NF), for example:
STUDENT (StudentID, Name, Address, DateOfBirth)
The primary key is StudentID; this is the only candidate key since Name is not guaranteed to be unique (two different students may have the same name). Address is also not guaranteed to be unique. There will be one student name per Student ID, since only one name is recorded for each student. There will be one address per Student ID; even though a student may have multiple address (e.g. a home address and a work address), the case study states that there is only a need to record the home address. There will be only one date of birth per Student ID. This means that Student ID functionally determines every other attribute in the table; therefore there are no repeating groups. There is a single simple candidate key, therefore no partial dependencies are possible. Name cannot be used to functionally determine any other attribute in the table since two different students may have the same name; likewise for address and date of birth, therefore there are no transitive dependencies in the table. Therefore it meets the requirements of first, second and third normal form.
• Create all the relations in a Microsoft Access database. Consider each attribute in every table and make appropriate choices regarding data types and sizes, indexes, required/not required, and validation rules. Your choices should be appropriate for each attribute and should support data integrity. (Note: see the Data Integrity section below for specific data integrity requirements.)
• Create relationships as appropriate. Enforce referential integrity for all appropriate relationships in the database. Explore the possibility of cascade update and/or delete options.
• Review the default indexes created by Access for each table. You should ensure that the indexes follow the guidelines given in the set textbook.
• Populate the database with sample data. You must include sufficient sample data to test your queries and report(s). You should aim for at least 5 or 6 rows in every table. Please note that the expected result of query questions may depend on the actual sample data populated.
• Create all the relations in a Microsoft Access database. Consider each attribute in every table and make appropriate choices regarding data types and sizes, indexes, required/not required, and validation rules. Your choices should be appropriate for each attribute and should support data integrity. (Note: see the Data Integrity section below for specific data integrity requirements.)
• Create relationships as appropriate. Enforce referential integrity for all appropriate relationships in the database. Explore the possibility of cascade update and/or delete options.
• Review the default indexes created by Access for each table. You should ensure that the indexes follow the guidelines given in the set textbook.
• Populate the database with sample data. You must include sufficient sample data to test your queries and report(s). You should aim for at least 5 or 6 rows in every table. Please note that the expected result of query questions may depend on the actual sample data populated.
Data integrity
You are required to implement integrity constraints within the database to enforce the following requirements:
For the table Currency:
• All fields must be entered.
• Currency code must be consisted of 3 letters.
• Current interest rate must be in the range 1% and 100%.
For the table Review:
• All fields must be entered.
• The only valid values for a recommendation must be "Buy", "Sell", or "Hold".
• Reason field can't be null.
For the table Trade:
• All fields must be entered.
• The quantities traded must be not less than 1000 units.
• Transaction date can't be future-dated.
Information requests
Create queries to answer the following information requests. Note: Do not use the Access query builder (QBE) to create your queries - you should type the queries manually using SQL view.
Although it is optional to justify the effectiveness of your SQL statement for the request, the marking process of MS Access queries for information requests may also consider your justification of the effectiveness for your queries in the implementation report if you provide them. (Expected results using sample data for queries depend on your actual sample data populated in the database. The following results of queries are only shown as examples. It is important that your SQL statement must reflect the correct business logic & SQL syntax.)
1. Show a list of all recommendations made after 1st January 2015 on the currencies of US dollar and Euro. Display the details including analyst name, currency code, recommendation, review date and reason.
Query 1 Result
|
FirstNamelLastName urrencyCode
|
RecommendationlReviewDate Reaso
|
|
John
|
'Johnson 'USD
|
'Buy
|
6/03/2015 'Fed financial QE policy
|
|
Joshua
|
'Andersen IEUR
|
'Buy
|
15/02/2015 Forecast Eurozone GDP increase
|
|
Linda
|
'Aka IEUR
|
'Hold
|
2/01/2015 'Euro central bank interest decision
|
2. Which portfolio has least amount of available cash? List the details including portfolio number, available cash, host client name, contact phone, and the responsible manager name.
|
Query 2 Result
PortfoliolD AvailableCash ClientName ContactPhon ManagerName
1007 $50,000.00 Dennis Dale148007761 ken King
|
3. List the currency or currencies that have two or more ratings.
Query 3 Result
CurrencyName InterestRate NbrOfRating
Euro 0.00% 1
US Dollar 1.00% 2
4. How many trades has each manager performed? Show the manager's name, contact phone and the total number of trades performed. Order the list so that the manager with the most trades appears first.
FirstName Lastname ContactPhone NbrOfTransaction
Ken King 0749554433 4
Mark Pauline 0748090945 2
Jeff Lincoln 0748090161 2
Fred Hoffman 0748095613 1
David king 0748505033 1
5. List details that managers have bought the currency of British pound and Japanese Yen in second half year of 2014. These details include manager's name, currency name, quantities purchased, exchange rate and transaction date.
Query 5
|
FirstNamelLastNamelCurrencyName
|
QuantityPurchasedlExchangeRatelftransactionDate
|
|
|
Fred
|
Hoffman Japanese Yen
|
110001
|
0.01
|
5/11/2014
|
|
Ken
|
King 'British Pound
|
2000001
|
1.65
|
5/10/2014
|
|
Ken
|
King 'British Pound
|
40001
|
1.581
|
9/09/2014
|
6. Find out three currencies that have been most frequently traded. Your query result should include the currency code, currency name, interest rate and how many times they have been traded. Sort the result by the number of times they were traded (Hint, using top, count functions.)
Query 6
|
CurrencyCodel CurrencyName
|
InterestRatelNbrOfTimesTradedd
|
|
GBP
|
'British Pound
|
2.0096r
|
|
|
.NZD
|
'New Zealand Dollar
|
2.00961
|
2
|
|
EUR
|
'Euro
|
0.00961
|
2
|