ISYS5000 Designing Database Systems Assignment Help

ISYS5000 Designing Database Systems Assignment Help

ISYS5000 Designing Database Systems Assignment Help

Normalisation of ER

ISYS5000 Designing Database Systems Assignment HelpFigure 1: 1 NF normalisation

ISYS5000 Designing Database Systems Assignment HelpFigure 2 NF normalisation

ISYS5000 Designing Database Systems Assignment Help

Figure 3: 3NF normalisation

Get More Information - Database Assignment Help

Relational diagram

ISYS5000 Designing Database Systems Assignment Help

Figure 4: relational model

Physical ER-diagram

ISYS5000 Designing Database Systems Assignment Help

Figure 5: ER-diagram

Table instance charts

Employee table

Column

Type and length

Null

constraint

other

employeeID

number(50)

No

PK

Uniquely identifies the each employee tupple

First_name

Varchar(100)

No

  

last_name

number(50)

No

  

sex

number(50)

No

  

salary

number(50)

NO

  

Commission percent

number(50)

No

  

Hire_date

Date

No

  

OfficeID

number(50)

No

office->officeId

Connects with office employee

Tables that are implemented according ER designing:

Office table

Column

Type and length

Null

constraint

other

officeID

number(50)

No

PK

Uniquely identifies the each office tupple

Telephone_no

number(50)

No

  

suburb

Varchar(100)

No

  

Office-manager table

Column

Type and length

Null

constraint

other

officeID

number(50)

No

PK

 

managerID

number(50)

No

Employee->employeeID

 

Office-property table

Column

Type and length

Null

constraint

other

officeID

number(50)

No

PK

 

PropertyID

number(50)

No

property->propertyID

 

Property table

Column

Type and length

Null

constraint

other

propertyID

number(50)

No

PK

Uniquely identifies the each property tupple

suburb

Varchar(100)

No

  

postcode

number(50)

No

  

street

number(50)

No

  

area

number(50)

NO

  

noofrooms

number(50)

No

  

listdate

Date

No

  

Lsit_price

number(50)

No

  

Date_sold

Date

No

  

Sold_price

number(50)

No

  

client table

Column

Type and length

Null

constraint

other

clientID

number(50)

No

PK

 

clientname

Varchar(100)

No

 

 Stores the name of each client

Client_contact table

Column

Type and length

Null

constraint

other

clientID

number(50)

No

Client->clientID

 

contactnumber

Number(50)

No

 

One client can have more than one contact number

Client-property table

Column

Type and length

Null

constraint

other

clientID

number(50)

No

Client->clientID

 

propertyID

number(50)

No

Property->propertyID

 

percentowned

Number(50)

no

  

Client-property table

Column

Type and length

Null

constraint

other

clientID

number(50)

No

Client->clientID

 

propertyID

number(50)

No

Property->propertyID

 

percentowned

Number(50)

no

  

employee-property table

Column

Type and length

Null

constraint

other

employeeID

number(50)

No

employee-> employee ID

 

propertyID

number(50)

No

Property->propertyID

 

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)

ISYS5000 Designing Database Systems Assignment HelpFigure 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

ISYS5000 Designing Database Systems Assignment Help

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

ISYS5000 Designing Database Systems Assignment Help

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

ISYS5000 Designing Database Systems Assignment Help

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

ISYS5000 Designing Database Systems Assignment Help

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)

ISYS5000 Designing Database Systems Assignment Help

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

ISYS5000 Designing Database Systems Assignment Help

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))

ISYS5000 Designing Database Systems Assignment Help

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

ISYS5000 Designing Database Systems Assignment Help

Figure 14: client table

Client_contact

ISYS5000 Designing Database Systems Assignment Help

Figure 15: client_contact table

Client_property

ISYS5000 Designing Database Systems Assignment Help

Figure 16: client_property table

Employee table

ISYS5000 Designing Database Systems Assignment Help

Figure 17: employee table

Employee_property

ISYS5000 Designing Database Systems Assignment Help

Figure 18:employee_property

Office

ISYS5000 Designing Database Systems Assignment Help

Figure 19: office table

Office_manager

ISYS5000 Designing Database Systems Assignment Help

Figure 20: office_manager

Office_suburb

ISYS5000 Designing Database Systems Assignment Help

Figure 21: office_suburb

Property

ISYS5000 Designing Database Systems Assignment Help

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