Database Exercises 3

A property company would like to implement a database with the following situation and criteria:

• They have properties (residential and commercial) from their clients for sale and lease
• Residential can be classified as public housing (HDB), executive condominiums, condominiums and private housing
• Each lease is minimum of 6 months as required by law for residential properties and 12 months for commercial properties
• Their clients can sell, buy, renting out or wanting to lease properties
• They can also be both selling and buying at the same time
• This company has in-house sales agent and is able to liaise with external sales agent (agents from other companies)
• Every agent has a licensed registration number as required by Council for Estate Agencies (CEA)
• Agent(s) can be active or inactive and has an expiry date on their licenses

a. Construct any 5 tables (of your choice) from the relational schema for the library database that you have identified from Question 1 above, in Oracle SQL; applying best practices in naming of tables, primary and foreign key constraints in your statements. You MUST show EVIDENCE of execution success through screen captures with your identity (e.g. user-account log-in) in the screen capture made clear with ALL syntax errors removed. You will need to insert appropriate values into the tables so that it will reflect the search results in the later part of the questions. Show your SQL statements inserting these values.

b. For each table (you may have up to a maximum of 5 tables), specify the following:

• Primary key
• Foreign key(s), if applicable
• Descriptive attributes

c. Construct an Entity Relation Diagram based on the scenario given. Identify all the entities, associative entities, attributes, primary key, relationships and constraints.

d. Write an Oracle SQL listing out all inactive agents AND their license expired.

e. Write the Oracle SQL listing out all commercial rental units, and their respective lessees that are due before 1st Nov 2019.

f. Write an Oracle SQL listing all clients that are both selling/leasing their properties AND looking for a property at the same time

g. Construct a data dictionary of the largest table that you have created.

Normalisation

h. Explain clearly what is normalisation and its importance.

Normalise the database you have constructed and you are to show and explain clearly all working steps from 1NF to 3NF. You may use the links below to understand what is normalisation.

i. Identify an object from Question 1 and list its object attributes.

j. Describe what is a class in Object Oriented and identify a class from Question 1. How is it a class for the object that you have chosen and explain.

k. As the company grows through the years, transaction data is also building up. Briefly describe how datamining can help the company to be at a competitive advantage.

l. If concurrency control is absent in this database, describe what will be the potential risk faced by the company.

m. Now this company wants to revamp the entire DBMS. Under DBLC, list out clearly all the objective, problems, constraints, scope and objectives, etc and how a new DBMS will or will not solve their problem(s).