Database Discussion 2022

Ruining head: DATABASE
Databases
Name of the Student:
Name of the Organization:
Author Note

1
DATABASE
Part 1: Revised ERD and schema
ERD
Discussion
The revised ERD has been created that would be helpful in distinguishing the members
who are attendees and the members which was not done in the previous version of the ERD. In
addition to this, the previous ERD had the information about the services which have been
omitted from this ERD as the information have been further manipulated and this ERD has been
developed in such a way so that values for the events and the members are authentic.

2
DATABASE
3NF Schema
Member (MemberID (pk), FirstName, LastName, PhoneNumber, MemberAddress, Suburb,
Skills)
Event (EventID (pk), Title, Leader, Date, Location, Type, PostCode, Instructions,
GuestAttendance, MemberAttendance, UnfilledPlaces)
Organizer (OragnizerID (pk), MemberID (fk), EventID (fk))
Attendees (AttendeeID (pk), MemberID (fk), EventID (fk))
Equipment (EquipmentID (pk), Name, Rate)
Loan (LoanID (pk), EquipmentID (fk), MemberID (fk), NoOfDays)
Part 2: Data dictionary
Member
Attribute DataType Size NULL/NOT
NULL
Key
MemberID number 10 NOT NULL Primary key
FirstName Varchar2 50 NOT NULL
LastName Varchar2 50 NOT NULL
MemberAddress Varchar2 50 NOT NULL
Suburb Varchar2 50 NOT NULL
Skill Varchar2 50 NOT NULL
Event

3
DATABASE
Attribute DataType Size NULL/NOT
NULL
Key
EventID number 10 NOT NULL Primary key
Title Varchar2 50 NOT NULL
Leader Varchar2 50 NOT NULL
EventDate Date NOT NULL
Location Varchar2 50 NOT NULL
Type Varchar2 50 NOT NULL
PostCode number 10 NOT NULL
Instruction Varchar2 150 NOT NULL
GuestAttendance Varchar2 50 NOT NULL
MemberAttendance Varchar2 50 NOT NULL
UnfilledPlaces Varchar2 50 NOT NULL
Attendee
Attribute DataType Size NULL/NOT
NULL
Key
AttendeeID number 10 NOT NULL Primary key
MemberID number 10 NOT NULL Foreign key
EquipmentID number 10 NOT NULL Foreign key

4
DATABASE
Organizer
Attribute DataType Size NULL/NOT
NULL
Key
OrganizerID number 10 NOT NULL Primary key
MemberID number 10 NOT NULL Foreign key
EventID number 10 NOT NULL Foreign key
Equipment
Attribute DataType Size NULL/NOT
NULL
Key
EquipmentID number 10 NOT NULL Primary key
Name Varchar2 50 NOT NULL
Rate number 10 NOT NULL
Loan
Attribute DataType Size NULL/NOT
NULL
Key
LoanID number 10 NOT NULL Primary key
MemberID number 10 NOT NULL Foreign key
EventID number 10 NOT NULL Foreign key
NoOfDays number 10 NOT NULL

5
DATABASE
Business Rules
 The database should be able to store the details of the members in the database which are
identified by their unique identities.
 The database should store the details of the events that are to take place in the system.
 The organizers details should be stored in the system
 There would be a number of attendees for each of the events and the details of the
attendees are to be stored in the system with attendeeid being the unique reference for
each of the attendee details.
 The details of the equipment and their loaning information are required to be stored in the
database each of which should identified uniquely.
Part 3: Implementation
Tables
Events
Create Table Event (EventID number(10) NOT NULL, Title varchar2(50) NOT NULL, Leader
varchar2(50) NOT NULL, EventDate DATE, Location varchar2(50) NOT NULL, Type
varchar2(50) NOT NULL, PostCode Number(10) NOT NULL, Instructions varchar2(150) NOT
NULL, GuestAttendance number(10) NOT NULL, MemberAttendance number(10) NOT
NULL, UnfilledPlaces number(10) NOT NULL,
CONSTRAINT event_pk PRIMARY KEY (EventID)
);

6
DATABASE
Members
Create Table Member (MemberID Number(10) NOT NULL, FirstName varchar2(50) NOT
NULL, LastName varchar2(50) NOT NULL, PhoneNumber varchar2(50) NOT NULL,
MemberAddress varchar2(50) NOT NULL, Suburb varchar2(50) NOT NULL, Skill
varchar2(50) NOT NULL,
CONSTRAINT member_pk PRIMARY KEY (MemberID)
);
Organizer

7
DATABASE
Create Table Organizer (OrganizerID number(10) NOT NULL, MemberID number(10) NOT
NULL, EventID number(10) NOT NULL, CONSTRAINT organization_pk PRIMARY KEY
(OrganizerID)
);
Attendee
Create Table Attendee (AttendeeID number(10) NOT NULL, MemberID number(10) NOT
NULL, EventID number(10) NOT NULL, CONSTRAINT attendee_pk PRIMARY KEY
(AttendeeID)
);
Equipment
Create Table Equipment(EquipmentID number(10) NOT NULL, Name varchar2(50) NOT
NULL, rate number(10) NOT NULL, CONSTRAINT equip_pk PRIMARY KEY
(EquipmentID)
);

8
DATABASE
Loan
Create table Loan (LoanID number(10) NOT NULL, EquipmentID number(10) NOT NULL,
MemberID number(10) NOT NULL, NoOfDays number(10) NOT NULL,
CONSTRAINT loan_pk PRIMARY KEY (LoanID)
);
Data
Member
insert into member(memberid, firstname, lastname, phonenumber, memberaddress, suburb, skill)
values(1,'daniel','james','11111990',' winston street','London','Gardner');
insert into member(memberid, firstname, lastname, phonenumber, memberaddress, suburb, skill)
values(2,'james','gosling','11111991',' rushell street','barmingham','flower picker');
insert into member(memberid, firstname, lastname, phonenumber, memberaddress, suburb, skill)
values(3,'daniel','victory','11111992',' wisdom street','Manchester','Gardner');

9
DATABASE
insert into member(memberid, firstname, lastname, phonenumber, memberaddress, suburb, skill)
values(4,'laravel','karjon','11111993',' tames street','Liverpool','flower picker');
insert into member(memberid, firstname, lastname, phonenumber, memberaddress, suburb, skill)
values(5,'victoria','james','11111994','legpool street','bristol','Gardner');
insert into member(memberid, firstname, lastname, phonenumber, memberaddress, suburb, skill)
values(6,'ruskin','bond','11111995',' abc street','York','flower picker');
insert into member(memberid, firstname, lastname, phonenumber, memberaddress, suburb, skill)
values(7,'deneza','james','11111996',' python street','London','Gardner');
insert into member(memberid, firstname, lastname, phonenumber, memberaddress, suburb, skill)
values(8,'daniel','hockings','11111997',' cde street','Oxford','flower picker');
insert into member(memberid, firstname, lastname, phonenumber, memberaddress, suburb, skill)
values(9,'hockings','james','11111998',' pqr street','Norwich','Gardner');
insert into member(memberid, firstname, lastname, phonenumber, memberaddress, suburb, skill)
values(10,'samuel','james','11111999',' rowdown street','Bristol','flower picker');
Event
insert into event(eventid, title, leader, eventdate, location, type, postcode, instructions,
guestattendance, memberattendance, unfilledplaces) values(1,'Chainsaw use and
maintainance','Fran Forklift',to_date('2019-08-23','yyyy-mm-dd'), 'Manchester','Skill and
technique',3384,'chainsaw maintanance',345,410,45);
insert into event(eventid, title, leader, eventdate, location, type, postcode, instructions,
guestattendance, memberattendance, unfilledplaces) values(2,'Decoration','Ruskin

10
DATABASE
Bond',to_date('2019-08-24','yyyy-mm-dd'), 'York','Open Garden',3385,'attandes are taken
around',346,411,46);
insert into event(eventid, title, leader, eventdate, location, type, postcode, instructions,
guestattendance, memberattendance, unfilledplaces) values(3,'flower decoration','daniel
victory',to_date('2019-08-25','yyyy-mm-dd'), 'London','Know your plants',3386,'cultivation of the
particular species',447,412,47);
insert into event(eventid, title, leader, eventdate, location, type, postcode, instructions,
guestattendance, memberattendance, unfilledplaces) values(4,'tree decoration','James
bond',to_date('2019-08-26','yyyy-mm-dd'), 'Bristol','Skill and technique',3387,'chainsaw
maintanance ',348,413,48);
insert into event(eventid, title, leader, eventdate, location, type, postcode, instructions,
guestattendance, memberattendance, unfilledplaces) values(5,'Chainsaw use and maintainance
','Andrw rusheel',to_date('2019-08-27','yyyy-mm-dd'), 'Oxford','Open garden',3388,' attandes are
taken around',349,314,49);
insert into event(eventid, title, leader, eventdate, location, type, postcode, instructions,
guestattendance, memberattendance, unfilledplaces) values(6,'flower decoration','Micheal
Von',to_date('2019-08-28','yyyy-mm-dd'), 'bristol','know your plant',3389,'cultivation of the
particular species ',350,415,50);
insert into event(eventid, title, leader, eventdate, location, type, postcode, instructions,
guestattendance, memberattendance, unfilledplaces) values(7,'Garden decoration','Andrew
flintop',to_date('2019-08-29','yyyy-mm-dd'), 'Manchester','Skill and technique ',3390,' Skill and
technique ',351,416,51);

11
DATABASE
insert into event(eventid, title, leader, eventdate, location, type, postcode, instructions,
guestattendance, memberattendance, unfilledplaces) values(8,'Chainsaw use and
maintenance','Kevin Peterson',to_date('2019-08-30','yyyy-mm-dd'), 'Birmingham','Open Garden
',3391,' attendees are taken around ',352,417,53);
insert into event(eventid, title, leader, eventdate, location, type, postcode, instructions,
guestattendance, memberattendance, unfilledplaces) values(9,'Tree decoration','Micheal
clerk',to_date('2019-08-31','yyyy-mm-dd'), 'bristol','know your plant',3390,' cultivation of the
particular species ',353,418,54);
insert into event(eventid, title, leader, eventdate, location, type, postcode, instructions,
guestattendance, memberattendance, unfilledplaces) values(10,'flower maintainance','Mathu
haden',to_date('2019-09-01','yyyy-mm-dd'), 'Manchester','Open garden',3381,'attandes are taken
around',355,419,55);
insert into event(eventid, title, leader, eventdate, location, type, postcode, instructions,
guestattendance, memberattendance, unfilledplaces) values(11, 'Chainsaw use and maintenance
','Shon Pollock',to_date('2019-09-02','yyyy-mm-dd'), 'London','know your plant',3382,'cultivation
of the particular species',456,420,56);
Organizer
Insert into organizer values (1,1,1);
Insert into organizer values (2,2,3);
Insert into organizer values (3,3,5);
Insert into organizer values (4,7,8);

12
DATABASE
Attendees
Insert into attendee values (1,1,4);
Insert into attendee values (2,1,2);
Insert into attendee values (3,2,6);
Insert into attendee values (4,3,6);
Insert into attendee values (5,2,9);
Insert into attendee values (6,2,9);
Insert into attendee values (7,3,9);
Insert into attendee values (8,7,10);
Insert into attendee values (9,1,10);
Insert into attendee values (10,2,11);
Insert into attendee values (11,3,10);
Insert into attendee values (12,7,11);
Equipment
insert into equipment values (1,'flower Scissors',30);
insert into equipment values (2,'flower Axe',40);
insert into equipment values (3,'Swevel',35);
Loan

13
DATABASE
Insert into loan values (1, 1, 1, 3);
Insert into loan values (2, 2, 1, 7);
Insert into loan values (3, 1, 3, 2);
Insert into loan values (4, 2, 1, 3);
Insert into loan values (5, 3, 3, 3);
Constraints
Attendee
Alter Table Attendee add constraint fk1_attendee Foreign Key (MemberID)
References Member (MemberID);
Alter Table Attendee add constraint fk2_attendee Foreign Key (EventID)
References Event (EventID);
Organizer
Alter Table Organizer add constraint fk1_organizer Foreign Key (MemberID)
References Member (MemberID);
Alter Table Organizer add constraint fk2_organizer Foreign Key (EventID)
References Event (EventID);
Loan
Alter Table Loan add constraint fk1_loan Foreign Key (MemberID)

14
DATABASE
References Member (MemberID);
Alter Table Loan add constraint fk2_loan Foreign Key (EquipmentID)
References Equipment (EquipmentID);
Grants
GRANT SELECT, INSERT, UPDATE, DELETE ON member TO MARKERTL;
GRANT SELECT, INSERT, UPDATE, DELETE ON event TO MARKERTL;
GRANT SELECT, INSERT, UPDATE, DELETE ON organizer TO MARKERTL;
GRANT SELECT, INSERT, UPDATE, DELETE ON attendee TO MARKERTL;
GRANT SELECT, INSERT, UPDATE, DELETE ON equipment TO MARKERTL;
GRANT SELECT, INSERT, UPDATE, DELETE ON loan TO MARKERTL;
It is to be noted that V is used for this database.
Part 4: Views
View A
Create view A As
Select * from member order by lastname;
View B
Create view B As

15
DATABASE
Select equipment.*, loan.NoOfDays, member.lastname, member.firstname from equipment
Inner join loan on loan.equipmentID = equipment. equipmentID
Inner join member on loan.memberID = member. memberID;
View C
Create View C As
Select member.LastName, member.FirstName From
Member
Inner Join Attendee on attendee.memberID = member.memberID
Inner Join Event on attendee.eventID = event.eventID
Where event.title = 'Chainsaw use and maintenance';
View D
Create View D As
Select * from event where guestattendance > memberattendance;
View E
Create View E As
Select * from event where eventdate = ADD_MONTHS(SYSDATE , 1 );
View F
Create View F As
Select * from event

16
DATABASE
Where title LIKE '%roses';
View G
Create View G As
Select COUNT(Organizer.OrganizerID), COUNT(Attendee.AttendeeID),
Member.memberName
From Member
Inner Join Organizer On Organizer.memberID = member.memberID
Inner Join Attendee On Attendee.memberID = member.memberID
HAVING COUNT(Organizer.OrganizerID) > COUNT(Attendee.AttendeeID)
Group by Member.memberName;
View H
Create View H As
Select * from event;
View I
Create View I As
Select attendee.*, event.*, member.*
From member
Inner join attendee On attendee.memberID = member.memberID
Inner join event On attendee.eventID = event.eventID

17
DATABASE
Which event.location NOT IN (Select suburb from member);
View J
Create View J As
Select unfilledplaces from event;

18
DATABASE
Bibliography
Kim, S., Thiessen, P. A., Bolton, E. E., Chen, J., Fu, G., Gindulyte, A., ... & Wang, J. (2015).
PubChem substance and compound databases. Nucleic acids research, 44(D1), D1202-
D1213.