ISYS5000 Designing Database Systems Proof Reading Service

ISYS5000 Designing Database Systems Proof Reading Service

Normalisation of ER

Figure 1: 1 NF normalisation

Figure 2 NF normalisation

Figure 3: 3NF normalisation

Relational diagram

Figure 4: relational model

Physical ER-diagram

Figure 5: ER-diagram

Table instance charts

Tables that are implemented according ER designing:

Reporting scripts

Query 1:

SELECT dbo.employee.last_name, dbo.office.suburb AS [office suburb], dbo.property.list_price, dbo.property.suburb AS [property suburb]

FROM     dbo.employee INNER JOIN

                  dbo.office ON dbo.employee.officeID = dbo.office.officeID INNER JOIN

                  dbo.property ON dbo.employee.propertyID = dbo.property.propertyID

WHERE (DATEDIFF (MONTH, dbo.employee.hiredate, GETDATE()) BETWEEN 3 AND 10) AND (dbo.property.date_sold IS NULL)

Figure 6:query 1

Query 2:

SELECT {fn CONCAT(dbo.employee.first_name, dbo.employee.last_name) } AS NAME, COUNT(dbo.employee_property.propertyID) AS count

FROM     dbo.employee INNER JOIN

                  dbo.employee_property ON dbo.employee.employeeID = dbo.employee_property.employeeID INNER JOIN

                  dbo.property ON dbo.employee_property.propertyID = dbo.property.propertyID

WHERE  (dbo.property.date_sold IS NOT NULL)

GROUP BY dbo.employee.first_name, dbo.employee.last_name

Figure 7: query 2

QUERY 3

SELECT SUM(employee.salary) AS [TOTAL income of employees], office.suburb AS [office suburb], (SELECT CONCAT(employee.first_name,employee.last_name)  FROM employee WHERE employee.employeeID=office_mangaer.managerID) AS[office manager] FROM employee INNER JOIN office INNER JOIN office_mangaer on office.officeID=office_mangaer.officeID  on employee.officeID=office.officeID GROUP BY employee.officeID,office.suburb, office_mangaer.managerID

Figure 8:query 3

Query 4

SELECT COUNT(employee.employeeID) AS [no of employees], office.suburb AS [office suburb], COUNT(employee.commissionpercent) AS [no of agents],COUNT(employee.employeeID)-COUNT(employee.commissionpercent) AS [no. of non agents] FROM employee INNER JOIN office on employee.officeID=office.officeID GROUP BY employee.officeID, office.suburb

Figure 9: query 4

Query 5

SELECT COUNT (employee.employeeID) AS [no of employees], employee.officeID FROM employee INNER JOIN office on employee.officeID=office.officeID GROUP BY employee.officeID, office.suburb

Figure 10: query 5

Query 6

SELECT employee_property.propertyID, employee.last_name, office.suburb, (SELECT employee.last_name FROM employee WHERE employee.employeeID=office_mangaer.managerID) AS manager FROM employee_property INNER JOIN employee INNER JOIN office_mangaer on employee.employeeID=office_mangaer.managerID INNER JOIN office INNER JOIN office_suburb on office.officeID=office_suburb.officeID on employee.officeID=office.officeID on employee_property.employeeID=employee.employeeID WHERE employee_property.propertyID NOT IN (SELECT office_suburb.propertyID FROM office_suburb)

Figure 11: query 6

Query 7

SELECT client.client_fname, COUNT(client_property.propertyID) AS properties FROM client INNER JOIN client_property on client.clientID=client_property.clientID GROUP BY client.client_fname

Figure 12: query 7

Query 8

SELECT list_price, MONTH(Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0))AS [in market] , street, CONCAT(client.client_fname,client.client_lname) AS NAME,property.suburb,client_property.percentowned

FROM     dbo.property INNER JOIN client_property INNER JOIN client on client_property.clientID=client.clientID on property.propertyID=client_property.propertyID

WHERE (date_listed >  Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0))

Figure 13: query 8

Manipulation scripts

A property owned by a new client has to be listed. Write scripts that will:

a.Record the property and assign the property to the office (and its manager) in the property’s suburb.

1st query

INSERT [dbo].[property] ([propertyID], [no_of_rooms], [date_listed], [list_price], [date_sold], [sold_price], [suburb], [street], [postcode], [area]) VALUES (5, CAST(4 AS Numeric(18, 0)), CAST(N'2017-04-29' AS Date), CAST(20000 AS Numeric(18, 0)), NULL, NULL, N'                                                         Seaton                                     ', N'riverdale', CAST(43242 AS Numeric(18, 0)), N'354')

2nd query

INSERT [dbo].[employee_property] ([employeeID], [propertyID]) VALUES (5, 5)\

b.Record the owner details.

1st query

INSERT [dbo].[client] ([clientID], [client_fname], [client_lname]) VALUES (4, N'dickles                                                                                             ', N'sam                                                                                                 ')

2nd query

INSERT [dbo].[client_property] ([clientID], [propertyID], [percentowned]) VALUES (4, 5, CAST(50 AS Numeric(18, 0)))

 An agent has left the company. Write scripts that will:

a.Remove details of the agent.

delete from employee where employee.employeeID=6

b.Re-assign the properties assigned to the agent to the manager of the agent’s office

INSERT INTO [employee_property]([employee_property].propertyID,[employee_property].employeeID)values ((Select employee_property.propertyID from employee_property INNER JOIN employee on employee_property.employeeID<>employee.employeeID),(select office_mangaer.managerID from office_mangaer INNER JOIN office_suburb INNER JOIN office on office_suburb.officeID=office.officeID on office_mangaer.officeID=office_suburb.officeID where office.suburb LIKE '%seaton' ) )

Table Data

Client table

Figure 14: client table

Client_contact

Figure 15: client_contact table

Client_property

Figure 16: client_property table

Employee table

Figure 17: employee table

Employee_property

Figure 18:employee_property

Office

Figure 19: office table

Office_manager

Figure 20: office_manager

Office_suburb

Figure 21: office_suburb

Property

Figure 22: property table

References

1.Holmes, J. G. (2000). Social relationships: The nature and function of relational schemas. European Journal of Social Psychology, 30(4), 447-495.

2.Moldovan, G., & Valeanu, M. (2006). INTEGRITY CONSTRAINTS IN DISTRIBUTED DATABASES. Acta Universitatis Apulensis. Mathematics-Informatics, 11, 313-324.

3.Psarris, K. (2004). An experimental evaluation of data dependence analysis techniques. IEEE Transactions on parallel and distributed systems. Vol. 15, no. 3, pp. 196-213