Assumptions made during EER modeling

Assumption: –

• A Musician work as Music Teacher and each music teacher is related to one and only one Musician.
• A Musician arranges one or many Venue and each venue arrange by one and only one Musician.
• A location has one to many venues and each venue located in one and only one location.
• A venue has one or many venue music and each venue’s music is related to one and only one venue.
• A band has one or many venue’s music and each venue’s music is related to one and only one band.
• A venue’s music has one or many live music and each live music is related to one and only one Venue’s music.
• According to the case category of student divided according to age. Children and adults. So that student is superclass and children and adult is subclass.
• A student book one or many lesson and each lesson book by one or many students.
• According to the case study Music has two categories. One is live music and other is teaching music. So that Music is superclass and live music and teaching music are subclasses.
• A lesson has one or many teach music and each tech music is related to one and only one lesson.
• A lesson has one or many classes and each class is related to one and only one lesson.
• A class has one or many teach music and each teaches music is related to one and only one class.

create table Musicians

Musician_ID varchar(10) not null,

First_Name varchar(50) not null,

Last_Name varchar(50) not null,

Age integer not null,

Gender varchar(10) not null,

Phone varchar(15) not null,

Address varchar(200) not null,

Email varchar(100) not null,

primary key (Musician_ID)

————-2. Student table———————

Create table Student

Student_ID varchar(10) not null,

Fisrt_Name varchar(50) not null,

Last_Name varchar(50) not null,

Age integer not null,

Gender varchar(10) not null,

Phone varchar(15) not null,

Address varchar(200) not null,

Email varchar(100),

Primary key (Student

———3. Children table———————

create table Children

Student_ID varchar(10) not null,

Primary key (Student_ID),

foreign key (Student_ID) references Student(Student_ID)

)————4. Adult table———————

create table Adult

(

Student_ID varchar(10) not null,

Primary key (Student_ID),

foreign key (Student_ID) references Student(Student_ID)

————5. Music_Tecaher table———————

create table Music_Teacher

Musician_ID varchar(10) not null,

WWCC_No integer not null,

Start_Date date not null,

Expiry_Date date not null,

primary key (Musician_ID),

foreign key (Musician_ID) references Musicians(Musician_ID)

————6. Music table———————

create table Music

Music_ID varchar(10) not null,

Type varchar(100) not null,

Music_Name varchar(150) not null,

Description varchar(200),

primary key (Music_ID)

———-7. Live_Music table———————

create table Live_Music

Live_Music_ID varchar(10) not null,

primary key (Live_Music_ID),

foreign key (Live_Music_ID) references Music(Music_ID)

————8. Tech_Music table———————

create table Tech_Music

Tech_Music_ID varchar(10) not null,

primary key (Tech_Music_ID),

foreign key (Tech_Music_ID) references Music(Music_ID)

———–9. Lesson table———————

create table Lesson

Lesson_ID varchar(10) not null,

Tech_Music_ID varchar(10) not null,

Style_Of_Music varchar(200) not null,

Music_Name varchar(200) not null,

primary key (Lesson_ID),

foreign key (Tech_Music_ID) references Tech_Music(Tech_Music_ID)

———-10. Lession_Booking table———————

create table Lesson_Booking

Booking_ID integer not null,

Lesson_ID varchar(10) not null,

Student_ID varchar(10) not null,

Booking_Date date not null,

primary key (Booking_ID),

foreign key (Lesson_ID) references Lesson(Lesson_ID),

foreign key (Student_ID) references Student(Student_ID)

————11. Classes table———————

create table Classes

Class_ID varchar(10) not null,

Lesson_ID varchar(10) not null,

Musician_ID varchar(10) not null,

Class_DateTime TIMESTAMP not null,

Class_Day varchar(10) not null,

Students integer not null,

primary key (Class_ID),

foreign key (Lesson_ID) references Lesson(Lesson_ID),

foreign key (Musician_ID) references Musicians(Musician_ID)

——–12. Location table———————

create table Locations

Location_ID varchar(10) not null,

City varchar(50) not null,

State varchar(50) not null,

Postcode integer not null,

Suburb varchar(50) not null,

Address varchar(200) not null,

primary key (Location_ID)

————-13. Band table———————

create table Band

Band_ID varchar(10) not null,

Band_Type varchar(100) not null,

Band_name varchar(150) not null,

Team_Members integer,

primary key (Band_ID)

———–14. Venue table———————

create table Venue

Venue_No varchar(10) not null,

Musician_ID varchar(10) not null,

Location_ID varchar(10) not null,

Title varchar(200) not null,

DateTime TIMESTAMP not null,

primary key (Venue_No),

Student database table

foreign key (Musician_ID) references Musicians(Musician_ID),

foreign key (Location_ID) references Locations(Location_ID)

————–15. Venue_Music table———————

create table Venue_Music

S_No integer not null,

Venue_No varchar(10) not null,

Live_Music_ID varchar(10) not null,

Band_ID varchar(10) not null,

primary key (S_No),

foreign key (Venue_No) references Venue(Venue_No),

foreign key (Live_Music_ID) references Live_Music(Live_Music_ID),

foreign key (Band_ID) references Band(Band_ID)

) Insert data into tables: –

————–1. Musicians table———————

insert into Musicians values (‘M101′,’Robin’, ‘Cann’,27, ‘Male’,’5789798790′,’US’,’[email protected]‘);

insert into Musicians values (‘M102′,’janni’, ‘Cann’,37, ‘Male’,’5789790979′,’US’,’[email protected]‘);

insert into Musicians values (‘M103′,’nayina’, ‘Cann’,45, ‘female’,’5789678790′,’US’,’[email protected]‘);

insert into Musicians values (‘M104′,’yoyo’, ‘Cann’,28, ‘Male’,’9867798790′,’US’,’[email protected]‘);

insert into Musicians values (‘M105′,’bamini’, ‘Cann’,47, ‘female’,’0987698790′,’US’,’[email protected]‘);

———-2. Student table———————

insert into Student values (‘S101′,’vania’, ‘raan’,12,’male’,’6789876789′,’US’,’[email protected]‘);

insert into Student values (‘S102′,’ganni’, ‘raan’,22,’female’,’6789889799′,’US’,’[email protected]‘);

insert into Student values (‘S103′,’fiyana’, ‘raan’,17,’male’,’6568676789′,’US’,’[email protected]‘);

insert into Student values (‘S104′,’jaklin’, ‘raan’,24,’female’,’5678788976′,’US’,’[email protected]‘);

insert into Student values (‘S105′,’vina’, ‘raan’,25,’male’,’4679876789′,’US’,’[email protected]‘);

—————3. Children table———————

insert into Children values(‘S101’);

insert into Children values(‘S103’);

—————4. Adult table———————

insert into Adult values(‘S102’);

insert into Adult values(‘S104’);

insert into Adult values(‘S105’);

———–5. Music_Tecaher table———————

insert into Music_Teacher values(‘M101′, 67578, ’02-Jan-2018′,’01-Dec-2018’);

insert into Music_Teacher values(‘M103′, 67572, ’16-Jan-2018′,’22-Dec-2018’);

insert into Music_Teacher values(‘M105′, 67571, ’22-Jan-2018′,’23-Dec-2019’);

————-6. Music table———————

insert into Music values (‘MU101′,’rock’,’yanjosg hjbjk’,”);

insert into Music values (‘MU102′,’pop’,’ggukgy jgiyil’,”);

insert into Music values (‘MU103′,’jazz’,’,nbcfgty fui’,”);

insert into Music values (‘MU104′,’yani’,’dfuyy vijo’,”);

insert into Music values (‘MU105′,’classic’,’dty hliy’,”);

———-7. Live_Music table———————

insert into Live_Music values (‘MU101’);

insert into Live_Music values (‘MU102’);

insert into Live_Music values (‘MU103’);

———–8. Tech_Music table———————

insert into Tech_Music values (‘MU101’);

insert into Tech_Music values (‘MU104’);

insert into Tech_Music values (‘MU105’);

—————9. Lesson table———————

insert into Lesson values (‘L101′,’MU101′,’YN’,’fghjk’);

insert into Lesson values (‘L102′,’MU101′,’GH’,’DFGHJ’);

insert into Lesson values (‘L103′,’MU104′,’BN’,’KJHV’);

insert into Lesson values (‘L104′,’MU104′,’YH’,’SDFVH’);

insert into Lesson values (‘L105′,’MU105′,’MN’,’ERFGIH’);

————10. Lession_Booking table———————

insert into Lesson_Booking values (1,’L101′,’S101′,’01-July-2018′);

insert into Lesson_Booking values (2,’L102′,’S102′,’02-July-2018′);

insert into Lesson_Booking values (3,’L103′,’S103′,’02-July-2018′);

insert into Lesson_Booking values (4,’L104′,’S104′,’02-July-2018′);

insert into Lesson_Booking values (5,’L105′,’S105′,’04-July-2018′);

————–11. Classes table———————

insert into Classes values (‘C101′,’L101′,’M101′,’02-Sep-2018 09:50:16.78′,’mon’,17);

insert into Classes values (‘C102′,’L102′,’M102′,’02-Sep-2018 11:50:16.78′,’mon’,27);

insert into Classes values (‘C103′,’L103′,’M104′,’04-Sep-2018 09:50:16.78′,’wed’,17);

insert into Classes values (‘C104′,’L104′,’M101′,’05-Sep-2018 09:50:16.78′,’thr’,16);

insert into Classes values (‘C105′,’L105′,’M102′,’06-Sep-2018 09:50:16.78′,’fri’,12);

————12. Location table———————

insert into Locations Values(‘LC101′,’US’,’US’,6789,’US’,’US’);

insert into Locations Values(‘LC102′,’US’,’US’,6783,’US’,’US’);

insert into Locations Values(‘LC103′,’US’,’US’,6784,’US’,’US’);

insert into Locations Values(‘LC104′,’US’,’US’,6785,’US’,’US’);

insert into Locations Values(‘LC105′,’US’,’US’,6787,’US’,’US’);

————13. Band table———————

insert into Band values (‘B101′,’uh’,’xyz’,10);

insert into Band values (‘B102′,’kh’,’hjn’,11);

insert into Band values (‘B103′,’uo’,’klm’,12);

insert into Band values (‘B104′,’hj’,’xyp’,13);

—————14. Venue table———————

insert into Venue values (‘V101′,’M103′,’LC101′,’gaby nji’,’02-oct-2018 09:50:16.78′);

insert into Venue values (‘V102′,’M103′,’LC101′,’gaby nji’,’03-oct-2018 09:50:16.78′);

insert into Venue values (‘V103′,’M103′,’LC101′,’gaby nji’,’04-oct-2018 09:50:16.78′);

Children database table

—————-15. Venue_Music table———————

insert into Venue_Music values(1,’V101′,’MU101′,’B101′);

insert into Venue_Music values(2,’V101′,’MU102′,’B102′);

insert into Venue_Music values(3,’V102′,’MU101′,’B103′);

insert into Venue_Music values(4,’V103′,’MU103′,’B101′);

2.3  Queries: –

1. Count of all new students who have joined Kevin’s music after the 1st of July 2018.

select count(Student.Student_ID) as “number of students”

from Student, Lesson_Booking

where Student.Student_ID=Lesson_Booking.STUDENT_ID

and Lesson_Booking.BOOKING_DATE>’01-July-2018′;

List of all underage male musicians and their age sorted by the first name.

select *from Musicians

where AGE < 35

and GENDER=’Male’

order by FIRST_NAME;

iii. List of all teachers who have an expired Working With Children Check (WWCC), with names, expiry date and their age, sorted by date.

Select Music_Teacher.WWCC_NO, Concat(Musicians.First_Name, Musicians.Last_Name) as “Musician Name”,

Music_Teacher.EXPIRY_DATE,Musicians.AGE from Musicians, Music_Teacher

where Musicians.MUSICIAN_ID = Music_Teacher.MUSICIAN_ID

order by Music_Teacher.EXPIRY_DATE;

1. List of all current lesson bookings sorted by the style of music and the booking date (Most recent first). Hint: You may have to join various tables in SQL to achieve the desired output, lookup join command.

Select LESSON.LESSON_ID, LESSON.MUSIC_NAME,LESSON.STYLE_OF_MUSIC,LESSON_BOOKING.BOOKING_DATE

from LESSON, LESSON_BOOKING

where LESSON_BOOKING.LESSON_ID = LESSON.LESSON_ID

order by  LESSON.STYLE_OF_MUSIC, LESSON_BOOKING.BOOKING_DATE DESC;

(Research Required) A report on the students enrolled, the style of music and the teacher for the current calendar month. Current calendar month refers to the month in which this SQL query is run.

select Lesson.STYLE_OF_MUSIC, concat(Musicians.First_Name, Musicians.Last_Name) as “Teacher Name”,

Classes.Class_Datetime, classes.STUDENTS as “number of students”

from Lesson, classes, music_teacher, Musicians

where EXTRACT(Month from classes.CLASS_DATETIME)=EXTRACT(month from sysdate)

and Lesson.LESSON_ID = classes.LESSON_ID

and music_teacher.MUSICIAN_ID = classes.MUSICIAN_ID

and Musicians.MUSICIAN_ID = music_teacher.MUSICIAN_ID;

 Research Task

After being alarmed by the recent security incidents reported in the media, Kevin decides to employ a part-time System Administrator to manage system security.

• a) Identify 6 security threats to the proposed system (Choose a mix of internal/external, deliberate/unintentional threats).

1. Unmanaged sensitive data: –

All the data is stored together without any encryption. This sensitive data is stored in the same table without any encryption. It can be hacked or leaked to wrong hands

1. Weak audit: –

This database has no auditing capabilities enabled. So any user can make any changes and no one will change that what change was made by whom?

1. Denial of service : –

Denial of service attack can be made on this database.  Anyone can made an Denial of service attack on this database.

1. Exposure of Backup Data :

All table data backups must be encrypted. As all the backups also have very sensitive information which can be misused, if it is passed to any wrong hands.

1. Weak authentication:

Authentication of the system must be strong. Today, the world is full of hackers. So, authenticity of the database is a major issue.

1. SQL Injection:

SQL injection is a major culprit of most of the data loss that occurs. Our database must be able to handle SQL injection. Anyone running a script that can cause SQL Injection must be blocked.

Unmanaged sensitive data	6	4	Data stored without encryption
Weak audit	5	8	No audit in CRUD operations
Denial of service	4	5	Overloading for failing DB
Exposure of Backup Data	6	7	Backup not encrypted
Weak authentication	9	8	Authentication is poor and anyone can log in
SQL Injection	7	5	Sql injection to steal data

c) For each threat which of the risk controls would you recommend? Justify your choice. If you have opted for avoidance or mitigation of risk, clearly explain the policies, measures or strategies that need to be put in place to achieve the desired outcome.

1. Unmanaged sensitive data: –

Adult database table

All the data is stored together without any encryption. The data must be properly encrypted before storing it. Data which is very personal to consumer should not be stored together and should be stored separately in encrypted form.

Weak audit: –

Audit functionality must be added to the database. Whenever a person make any changes to the database like Insertion, Updating , Deletion etc. , proper auditing must be done by using Triggers etc. Proper data logs must be there for all the changes made to the database.

Denial of service : –

Denial of service attack can be made on this database. Proper rules must be made at the database admin end that blocks user permanently/temporarily whenever any user tries to make DOS attack on the database.

 Exposure of Backup Data: –

All table data backups must be encrypted. Database backups are usually stored in multiple or separate locations to handle unusual situations like loss because of Natural or human calamities. These backups also contain all the crucial information and can be used in evil ways if misused.

Weak authentication: –

Authentication of the system must be strong. Proper rules and policies must be made to ensure that only an authenticated user is using the system and no other user can enter or corrupt the System.

SQL Injection:

SQL injection is a major culprit of most of the data loss that occurs. Various measures can be taken at both the system and application end to ensure that the database is not prone to SQL Injection. This can also be achieved by the proper use of Procedures, triggers, views etc.

Alhir, S. (2003) Learning UML. Sebastopol, Calif.: O’Reilly.

Ambler, S. (2003) The elements of UML style. Cambridge: Cambridge U.P.

Ambler, S. (2005) The elements of UML 2.0 style. Cambridge [U.K.]: Cambridge University Press.

Awad, E. and Gotterer, M. (1992) Database management. Danvers, Mass.: Boyd & Fraser Pub. Co.

Belloc, H. (1967) On. Freeport, N.Y.: Books for Libraries Press.

Broad, W. (2007) Oracle. Penguin Group US.

Dennis, A., Wixom, B. and Tegarden, D. (2015) Systems Analysis and Design. New York: Wiley.

ELIOT, G. (2018) MILL ON THE FLOSS. [S.l.]: ALMA CLASSICS.

Harmon, P. and Sawyer (1999) UML for Visual Basic 6.0 Developers. San Francisco, Cal.: Morgan Kaufmann.

Holt, J. (2007) UML for systems engineering. London: The Institution of Electrical Engineers.

Kimmel, P. (2011) UML demystified. New York: McGraw Hill Professional.

Li, D. (1987) A PROLOG database system. Letchworth Herts.: Research Studies Press.

Mason, D. and Willcocks, L. (1994) Systems analysis, systems design. Henley-on-Thames: A. Waller.

Naiburg, E. and Maksimchuck, R. (2002) UML for database design. Boston: Addison-Wesley.

Obermair, W. (1995) Extending the object-oriented database management system VODAK with active capabilities. Sankt Augustin: GMD.

Oracle (1995) The Oracle speaks. Auckland, N.Z.: Oracle Productions.

PATHAK, N. (2011) DATABASE MANAGEMENT SYSTEM. [S.l.]: HIMALAYA PUBLISHING HOUSE.

Ramarkrishnan, R. (1997) Database management system. London: McGraw-Hill Pub. Co. (ISE Editions).

Satzinger, J., Jackson, R. and Burd, S. (2016) Systems analysis and design in a changing world. Boston: Cengage Learning.

Watson, I. (1998) Oracle. London: Vista.

Weilkiens, T. and Oestereich, B. (n.d.) UML 2 Certification Guide.