Questions 1 and 2 in this part relate to the ER model given below. The ER model illustrates the entities and relationships for modeling a stock research fine. Assume the firm has two types of staff ? trade manager and research analyst. Analysts make recommendations based on their researches. Examine the ER model below and answer all the questions that follow questions in this section relate to the ER model given below.

1. Data Modelling According to the ER model given above, answer either yes or no to the following questions: (a) Can a manager have more than one qualification?

(b) Is it possible for an analyst to recommend more than one stock?

(c) Can a staff member be both a manager and an analyst?

(d) Is it required for a stock that must be recommended by analysts?

2. Converting ER models Convert the ER model given in Part A into a set of relations that satisfy Third Normal Form (3NF). You do not need to show your workings. You do not need to justify that they are in 3NF at this stage. You do not need to show sample data. Just show your relations. You should write your relations in either format shown below:

The following table identifies the information that analysts make recommendations on shares based on their researches. But this table has been wrongly designed. It has been assumed that the combination of AnalystiD, StockCode and accommodDate uniquely identifies each tuple in this relation.

(a) On the above table, if we want to delete a record related to the recommendation on the stock- AGL, what kind of anomaly will happen? Justify your answer.

(b) What is the highest normal form that this relation satisfies and why?

(c) Normalise the relation into a set of relations that satisfy 3NF using the format as follows: Customer (CustomerlD, CustomerName) Order (OrderlD, Amount, Date, CustomerlD) Foreign key (CustomerlD) references Customer

Formulate SQL queries to answer the following information requests. Use the relations AGENT, PROPERTY, CLIENT, and VIEWING provided below. These relations describe clients viewing properties that they intend to rent. These properties are managed by specific agents in a property management company as shown in the tables.

Relations

AGENT(AgentlD FName, LName, Position, Phone)

PROPERTY(ProoertyNo, Street, Suburb, Rent, Agent1D)

Foreign key (AgentID) references Agent

CLIENT(ClientNo, FName, LName, Phone)

VIEWING(ChentNo PropertyNo, ViewDate Comment)

Foreign key (ClientNo) references Client

Foreign key (PropertyNo) references Property

Note that:

• You are asked to provide a general solution to each request. If the database contents change, each of your queries should continue to answer the information requested correctly.
• Simple queries are preferred; if your queries are unnecessarily complex you may lose marks.
• For the given sample data, your queries should be able to generate the same data and column names as shown in the result table for each request.
• You are not required to sort the results in any order unless requested.
• State any assumptions that you make to clarify your understanding of the information request.

i. Show properties that have not been viewed.

ii. Show the detail of agents who managed the properties in the suburb South Rocky with the rent less than $400.

iii. Show details of propoerties that have been viewed but didn't have comments.

iv How many properties does each agent manage? Show the agent's ID, name, phone and number of property managed. Display the results in ascending order of agent's first name.

v. Which property has the cheapest rent? Note: you will need to use the min () function.

Transcribed Image Text:

StaffID OfficeNo Phone StaffType "M" STAFF MANAGER {Qualification} StaffType= "A" ANALYST ExpertiseArea RECOMMENDATION RecommdID RecommdDate Recommendation STOCK Stock Code CompanyName Section ShareRecommendation AnalystID AnalystName James Cook A101 A101 A102 A102 A103 A104 James Cook Shirley Lin Shirley Lin David Johnson Linda Coles StockCode ORG RIO ORG BHP AGL RIO Company Name Recommendation RecommdDate Origin Energy Rio Tinto Limited Origin Energy BHP Billiton Buy Sell Buy Hold AGL Energy Buy Rio Tinto Limited Sell 12-1-2018 15-1-2018 15-1-2018 18-1-2018 19-2-2018 19-2-2018 AGENT AgentID 100 120 140 160 180 200 PROPERTY Property No P14 P94 P4 P36 P21 P16 FName Michael Ann David Mary Susan Julie Street 16 Holhead 6 Argyll St 6 LawrenceSt 2 Manor Rd 18 Dale Rd 5 Novar Dr LName Wu Beech Ford Howe Hall Lee Suburb Frenchville North Rocky South Rocky South Rocky Frenchville South Rocky Position Manager Assistant Phone 49001122 49778819 Supervisor 49232300 Supervisor 49018765 48090914 48003355 Manager Assistant Rent 750 400 335 390 450 400 AgentID 160 180 120 120 140 CLIENT ClientNo C76 C56 C74 C62 VIEWING ClientNo C56 C76 C56 C62 C56 C74 FName John Aline Mike Mary LName Kay Stuart Ritchie Tegear Property No P14 P4 P4 P14 P36 P16 Phone 49000111 49889977 49012288 49883344 ViewDate 24-May-18 20-April-18 26-May-18 14-May-18 28-April-18 15-April-18 Comment Too small Too remote No dining room OK Property No Street P21 18 Dale Rd P94 6 Argyll St Suburb Frenchville North Rocky Rent AgentID 450 120 400 180 AgentID FName LName Position Phone 120 Ann Beech Assistant 49778819 Property No P4 P36 Street Suburb 6 Lawrence St South Rocky 2 Manor Rd South Rocky Rent AgentID 335 390 120 AgentID FName LName 120 Ann Beech 140 David Ford 160 Mary Howe 180 Suzan Hall Phone 49778819 49232300 49018765 48090914 NbrProperty Managed 2 1 1 Property No P4 Street 6 Lawrence St South Rocky 335 Suburb Rent AgentID StaffID OfficeNo Phone StaffType "M" STAFF MANAGER {Qualification} StaffType= "A" ANALYST ExpertiseArea RECOMMENDATION RecommdID RecommdDate Recommendation STOCK Stock Code CompanyName Section ShareRecommendation AnalystID AnalystName James Cook A101 A101 A102 A102 A103 A104 James Cook Shirley Lin Shirley Lin David Johnson Linda Coles StockCode ORG RIO ORG BHP AGL RIO Company Name Recommendation RecommdDate Origin Energy Rio Tinto Limited Origin Energy BHP Billiton Buy Sell Buy Hold AGL Energy Buy Rio Tinto Limited Sell 12-1-2018 15-1-2018 15-1-2018 18-1-2018 19-2-2018 19-2-2018 AGENT AgentID 100 120 140 160 180 200 PROPERTY Property No P14 P94 P4 P36 P21 P16 FName Michael Ann David Mary Susan Julie Street 16 Holhead 6 Argyll St 6 LawrenceSt 2 Manor Rd 18 Dale Rd 5 Novar Dr LName Wu Beech Ford Howe Hall Lee Suburb Frenchville North Rocky South Rocky South Rocky Frenchville South Rocky Position Manager Assistant Phone 49001122 49778819 Supervisor 49232300 Supervisor 49018765 48090914 48003355 Manager Assistant Rent 750 400 335 390 450 400 AgentID 160 180 120 120 140 CLIENT ClientNo C76 C56 C74 C62 VIEWING ClientNo C56 C76 C56 C62 C56 C74 FName John Aline Mike Mary LName Kay Stuart Ritchie Tegear Property No P14 P4 P4 P14 P36 P16 Phone 49000111 49889977 49012288 49883344 ViewDate 24-May-18 20-April-18 26-May-18 14-May-18 28-April-18 15-April-18 Comment Too small Too remote No dining room OK Property No Street P21 18 Dale Rd P94 6 Argyll St Suburb Frenchville North Rocky Rent AgentID 450 120 400 180 AgentID FName LName Position Phone 120 Ann Beech Assistant 49778819 Property No P4 P36 Street Suburb 6 Lawrence St South Rocky 2 Manor Rd South Rocky Rent AgentID 335 390 120 AgentID FName LName 120 Ann Beech 140 David Ford 160 Mary Howe 180 Suzan Hall Phone 49778819 49232300 49018765 48090914 NbrProperty Managed 2 1 1 Property No P4 Street 6 Lawrence St South Rocky 335 Suburb Rent AgentID

Answer rating: 100% (QA)

1 Data Modeling a Yes Qualification is written in brackets b Yes Analyst and Recommendation relation ... View the full answer