Database System and Design

This document provides a conceptual and logical database design for a fashion show database, including enterprise rules and SQL queries.

Running head: DATABASE SYSTEM AND DESIGN
Database System and Design
Name of the Student:
Name of the University:
Author Note

1
DATABASE SYSTEM AND DESIGN
Stage 1: Scenario and Conceptual Database Design
Task 1.1: Selection of the case upon which the database design and
implementation is to be based
A fashion show database has been used here for the description of the database
designing and implementation. The main focus here has been put on the shows and the
designers and the models who are participating the shows. The database also involves the
description of the models and the outfits which are used in the fashion show. The database
should store the details of the designers in the system and also their designs. The designers
would be producing a number of designs for the fashion show. In addition to this, the details
of a number of systems are to be stored in the database.

2
DATABASE SYSTEM AND DESIGN
Task 1.2: Provide a conceptual database design for your scenario & the list of
enterprise rules being modelled
EER Diagram
Business Rules
The main business rules that were considered during the implementation of the
database are described below:
 The data base should be able to store the details of all the designers in the system and
the unique designer ids have been used for the separate designers in the database.
 The data base should be able to store the details of the shows which are held are
stored in the system.
 The data base should include the details of the outfits designed by a particular
designers and are to identified with the unique OutfitID and each outfit is to be
referenced to a particular designer.

3
DATABASE SYSTEM AND DESIGN
 The data base should include the details of the models who would be participating in
the shows and would be identified by their unique model id.
Stage 2: Logical Database Design and Oracle SQL
Implementation/querying
Task 2.1: Provide a Logical Database Design for your scenario
Fashion Show (ShowID (pk), ModelID (fk), OutfitID (fk), ShowDate, Location)
Designers (DesignerID (pk), Name, Country)
Model (ModelID (pk), Name, Age, Gender)
Outfit (OutfitID (pk), Genre, DesignerID (fk))
Task 2.2: Create the tables using Oracle DBMS
Database tables
Fashion Show
Designers

4
DATABASE SYSTEM AND DESIGN
Model
Outfit

5
DATABASE SYSTEM AND DESIGN
Task 2.3: Create the four most useful indexes on your tables
Fashion Show
Designers
Model
Outfit

6
DATABASE SYSTEM AND DESIGN
Task 2.4: Data Population
Fashion Show
Designers
Model

7
DATABASE SYSTEM AND DESIGN
Outfit
Task 2.5: SQL Query writing
Query 1
Select COUNT(P2507387MODEL.MODELID)
From P2507387MODEL
Where P2507387MODEL.GENDER = 'Female';

8
DATABASE SYSTEM AND DESIGN
The query provides the number of female models who would participating in the
fashion show.
Query 2
Select P2507387MODEL.NAME
From P2507387MODEL
Inner Join P2507387FASHIONSHOW ON P2507387FASHIONSHOW.MODELID =
P2507387MODEL.MODELID
Inner Join P2507387OUTFIT ON P2507387FASHIONSHOW.OUTFITID =
P2507387OUTFIT.OUTFITID
Where P2507387OUTFIT.GENRE = 'Traditional';
The result displayed above provides the details of the name of the models who would
wear the traditional outfit at the show.
Query 3
Select P2507387DESIGNER.NAME

9
DATABASE SYSTEM AND DESIGN
From P2507387DESIGNER
Inner Join P2507387OUTFIT ON P2507387DESIGNER.DESIGNERID =
P2507387OUTFIT.DESIGNERID
Where P2507387OUTFIT.GENRE = 'Traditional';
The table created above provides the names of the designers who creates the
traditional outfits.
Query 4
Select P2507387MODEL.NAME, P2507387OUTFIT.GENRE
From P2507387MODEL
Inner Join P2507387FASHIONSHOW On P2507387FASHIONSHOW.MODELID =
P2507387MODEL.MODELID
Inner Join P2507387OUTFIT On P2507387OUTFIT.OUTFITID =
P2507387FASHIONSHOW.OUTFITID;

10
DATABASE SYSTEM AND DESIGN
The names of the models and the their outfits have been displayed in the result above
using the query described above.
Query 5
Select P2507387DESIGNER.NAME, P2507387OUTFIT.GENRE
From P2507387DESIGNER
Inner Join P2507387OUTFIT On P2507387OUTFIT.DESIGNERID =
P2507387DESIGNER.DESIGNERID;
The names of the designers participating in the shows and the type of clothing that
they design has been described in the table displayed here with the help of the query
described above.
Query 6
Select Name

11
DATABASE SYSTEM AND DESIGN
From P2507387DESIGNER
Where COUNTRY = 'England';
The name of all the designers from England have been displayed in table
above using the query designed above.

12
DATABASE SYSTEM AND DESIGN
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Hu, Y., 2016. Design and Implementation of Recruitment Management System Based on
Analysis of Advantages and Disadvantages of PHP Three-Tier. Romanian Review Precision
Mechanics, Optics & Mechatronics, (49), p.74.
Lee, S., Tewolde, G. and Kwon, J., 2014, March. Design and implementation of vehicle
tracking system using GPS/GSM/GPRS technology and smartphone application. In Internet
of Things (WF-IoT), 2014 IEEE World Forum on (pp. 353-358). IEEE.
Pfaff, B., Pettit, J., Koponen, T., Jackson, E., Zhou, A., Rajahalme, J., Gross, J., Wang, A.,
Stringer, J., Shelar, P. and Amidon, K., 2015. The design and implementation of open
vswitch. In 12th {USENIX} Symposium on Networked Systems Design and Implementation
({NSDI} 15) (pp. 117-130).