ICT285 Database Development and Management Assignment - 2020

This document presents a comprehensive solution to a database development and management assignment. It begins with relational algebra expressions, demonstrating proficiency in database querying. The solution then provides a series of SQL queries, demonstrating the ability to retrieve and manipulate data from a database. Further, it delves into the creation of SQL tables, showcasing an understanding of database schema design. The assignment also explores database normalization, identifying and addressing data redundancy issues. The document includes the creation of tables and applying normalization principles to improve database structure and efficiency. Finally, it includes a conceptual design using an Entity-Relationship Diagram (ERD) for a hospital database, illustrating the ability to model real-world scenarios. The solution concludes with a bibliography of relevant sources.

Running head: DATABASE DEVELOPMENT AND MANAGEMENT
DATABASE DEVELOPMENT AND MANAGEMENT
Name of the Student
Name of the University
Author Note

1DATABASE DEVELOPMENT AND MANAGEMENT
Table of Contents
Relational algebra:.....................................................................................................................2
SQL queries:...............................................................................................................................3
Further SQL...............................................................................................................................8
Normalization:..........................................................................................................................10

2DATABASE DEVELOPMENT AND MANAGEMENT
Relational algebra:
a.
 LecturerName, UniversityName (LECTURER)
b.
 LectureName (σ Topic = ‘Biological Diversity’ (LECTURE))
c.
 StudentName (σ Topic = ‘Biological Diversity’ or Topic = ‘Bugs and Bubs’ (UNIVERSITY * U.UniversityName =
S.UniversityName STUDENT) * (U.UniversityName = LC.UniversityName LECTURER) * LC.LecturerName = L. LecturerName
LECTURE)))
d.
 StudentName (σ Country = ‘Malaysia’ (UNIVERSITY * U.UniversityName = S.UniversityName STUDENT)*
U.UniversityName = L.UniversityName LECTURER)
e.
 StudentName (σ UniversityName = ‘Murdoch University’, AND country = ‘Italy’ AND Topic = ‘Biological Diversity’
(UNIVERSITY * U.UniversityName = S.UniversityName STUDENT) * (U.UniversityName = LC.UniversityName
LECTURE) * LC.LecturerName = L. LecturerName LECTURE)))
f.
 LectureName, LectureNo, Topic, StudentName (UNIVERSITY * U.UniversityName = S.UniversityName STUDENT)
* (UNIVERSITY U.UniversityName = LC.UniversityName LECTURER) * LECTURER LC.LecturerName = L.
LecturerName LECTURE)))
g.

3DATABASE DEVELOPMENT AND MANAGEMENT
 StudentName (σ LectureName = ‘Introduction to Biology’ AND LectureName = ‘Darwin 101’ (UNIVERSITY *
U.UniversityName = S.UniversityName STUDENT) * (UNIVERSITY U.UniversityName = LC.UniversityName
LECTURER) * LECTURER LC.LecturerName = L. LecturerName LECTURE)))
h.
 LecturerName, Biography, UniversityName (σ Country = ‘Greece’ (UNIVERSITY * U. UniversityName = LC.
UniversityName LECTURER))
i.
 StudentName (STUDENT) MINUS ( StudentName (σ LectureName = ‘Grassland Cultivation Masterclass’
(UNIVERSITY * U.UniversityName = S.UniversityName STUDENT) * (UNIVERSITY U.UniversityName =
LC.UniversityName LECTURER) * LECTURER LC.LecturerName = L. LecturerName LECTURE))))
j.
 StudentName (STUDENT S.StudentNo = P.StudentNo PARTICIAPANT)
SQL queries:
A. select w.workid, w.title,w.copy,w.medium, w.description, w.artistid, (FIRSTNAME ||
' ' || LASTNAME) AS ARTISTNAME from dtoohey.work w
JOIN DTOOHEY.artist a on w.artistid =a.artistid WHERE w.description LIKE
'%Surrealist%';
B. select w.workid, w.title, w.copy, w.description, (FIRSTNAME || ' ' || LASTNAME)
AS ARTISTNAME ,
t.AcquisitionPrice,t.AskingPrice from dtoohey.work w

4DATABASE DEVELOPMENT AND MANAGEMENT
join dtoohey.artist a on w.artistid=a.artistid
join dtoohey.TRANS t on w.workid=t.workid
where t.AskingPrice >400;
C. SELECT w.title
FROM dtoohey.WORK W
GROUP BY w.title
HAVING COUNT(w.title) =2;
D. SELECT (FIRSTNAME || ' ' || LASTNAME) AS ARTIST_NAME, (a.datedeceased -
a.dateofbirth) as Death_At_Age from dtoohey.ARTIST A;

5DATABASE DEVELOPMENT AND MANAGEMENT
E. SELECT a.artistid, (a.firstname||' '||a.lastname) as fullname, count(w.workid) as
Number_of_works
FROM dtoohey.artist a, dtoohey.work w where a.artistid=w.artistid group by
(a.artistid,a.firstname,a.lastname)
order by count(w.workid) asc;
F. SELECT
w.workid,w.title, (a.firstname||' '||a.lastname)as artistname
FROM dtoohey.work w
inner join dtoohey.artist a on w.artistid =a.artistid

6DATABASE DEVELOPMENT AND MANAGEMENT
inner join dtoohey.trans t on w.workid=t.workid
where
t.salesprice > (select avg(t.salesprice) from dtoohey.trans);
G. SELECT extract (year from T.DATESOLD) as YEAR , SUM(T.SALESPRICE)as
TOTALAMUNTOFSALES,COUNT(T.WORKID)AS numberofwork FROM
dtoohey.TRANS T GROUP BY extract (year from T.DATESOLD);
H. select (a.firstname||' '||a.lastname)as artistname, count(w.workid) as TotalSoldWork
from dtoohey.WORK w
inner join dtoohey.ARTIST a
on w.ArtistID = a.artistid
group by (a.firstname,a.lastname)
HAVING count(w.workid)=(

7DATABASE DEVELOPMENT AND MANAGEMENT
SELECT MAX(T.TOTALSOLDWORK) FROM (select (a.firstname||' '||a.lastname)as
artistname, count(w.workid) as TotalSoldWork from dtoohey.WORK w
inner join dtoohey.ARTIST a
on w.ArtistID = a.artistid
group by (a.firstname,a.lastname)) T);
I. select SUM(t.salesprice - t.acquisitionprice) as TotalProfit from dtoohey.trans t;
J. select m.customername from (select (c.firstname||' '||c.lastname)as customername,
count(cai.artistid)
from dtoohey.CUSTOMER_ARTIST_INT cai
inner join
dtoohey.customer c
on cai.customerid=c.customerid
inner join dtoohey.artist a
on a.artistid=cai.artistid

8DATABASE DEVELOPMENT AND MANAGEMENT
group by (c.firstname,c.lastname)
having count(cai.artistid)=
(select count(a.artistid) from DTOOHEY.artist a)) m;
Further SQL
A. CREATE TABLE PRESENTER
(
PresenterNo NUMBER NOT NULL,
PresenterName CHAR (50) NOT NULL,
Biography VARCHAR2 (200)NOT NULL,
InstitutionName CHAR (50)NOT NULL,
CONSTRAINT pk_PresenterNo_pk PRIMARY KEY(PresenterNo)
);
B. CREATE TABLE LECTURE

9DATABASE DEVELOPMENT AND MANAGEMENT
(
LectureName CHAR (20) NOT NULL,Description VARCHAR2 (200)NOT
NULL,LectureNo number NOT NULL,Theme CHAR (20)NOT NULL,Capacity NUMBER
NOT NULL,DateAndTime TIMESTAMP NOT NULL,PresenterNo NUMBER NOT
NULL,CONSTRAINT PK_LectureNo_PK PRIMARY KEY(LectureNo), CONSTRAINT
FK_PresenterNo_FK FOREIGN KEY (PresenterNo) REFERENCES PRESENTER
(PresenterNo) ON DELETE CASCADE
);
C. INSERT INTO PRESENTER
(PresenterNo, PresenterName, Biography,InstitutionName)VALUES
(111,'Huang Sheng','A part time bachelor degree student who working full time as
Analyst.','Murdoch University' );
D. alter table lecture add VenueName CHAR(1) constraint Possible_Venue_Check check
(VenueName IN (' A',' B',' C'));

10DATABASE DEVELOPMENT AND MANAGEMENT
E. update lecture set capacity =capacity +10;
Normalization:
a.
The existing table is not properly normalized and provides several problems including
data redundancy. This database has a lack of standards and evaluating is database is difficult.
Existing database will face several problems when a user tries to add details of particular
information like podiatrist information, patient information or room information. Updating
this type of database is not easy because updating a particular row can create many problems.
When a person will get much error when he/she tries to update or delete specific information.
An existing database can contain many duplicate data and retrieving one single information
from this database would require a long time. This database acquires huge space in the
database.
A person cannot store StaffNo, Podiatrist, PatientName, PatNum, PatTelNo, ApptDateTime,
RoomNo and RoomExt separately. They need to store all the information at the time. The
existing database returns many redundant data.

11DATABASE DEVELOPMENT AND MANAGEMENT
A person will face many abnormalities problem while they are inserting, delete or
modify the database. Along with the primary key many important constraints are missing in
the existing database.
b.
We can resolve the above problems by using the normalization method. We can
simply divide the existing table and create new tables and store more structure data. After
creating three new tables data anomalies problem can be resolved. A person can insert,
update and delete a specific row. Three tables are podiatrist, patient and room table. Podiatrist
table will store staffid, and name. Staff id will be the primary key in the podiatrist table. The
second table is a patient table. A patient table can store every unique number of patients with
their telephone number and appointment date. Room table will storeroom no and their
extension number. The third normal form is the best way to eliminate data anomalies. After
converting the existing table into third normal form, the database will be more efficient, and
data redundancy can be reduced.
Conceptual Design:
1.