Chat with us, powered by LiveChat Applied Database Systems | acewriters
+1(978)310-4246 credencewriters@gmail.com
  

1. Revise your design, if necessary.2.Translate your ER diagram into Relational SchemaWrite up a relational schema that is equivalent to your ER diagram. Make sure that you translate not only entities but also relationships. Write a short explanation for each relation. For example,OrdersOrders(ordernum: INTEGER, cid: INTEGER, cardnum: CHAR(16), cardmonth: INTEGER, cardyear: INTEGER, order_date: DATE, ship_date: DATE)This table stores one entry for each order the user places. It only includes general information about the transaction itself, and not the specific items purchased by the user. The cid of the user placing the order as well as their credit card number and credit card date are stored here. In addition, we can track when the order was submitted as well as when it shipped.3.Implementation of the Schema in MariaDBWrite an SQL script with the commands to create the tables of your database.The CREATE TABLE statements must specify: o Appropriate types for the attributes;The primary key; o Constraints such as NOT NULL and UNIQUE whenever appropriate; o Default values where appropriate;FOREIGN KEY constraints, together with the policy for reacting to changes (remember that the default is ON DELETE NO ACTION)Write comments into the script that explain the rationale behind the definition of your constraints.4.Loading DataLoad interesting data into the database. In principle, you can do this either by writing many INSERT statements, or by creating a file that contains the data that you want to fill into a relation and to load the file content using theApplied Database Systems IT 530: Projectbulk loader as we did in Homework 4. Each relation should have at least 8 records.
3._schemaandsqlimp_sample_8868_.pdf

3._schemaandsqlimplementation_8866_.pdf.docx

collegedbms_erdiagramfeedback_9051_.pdf

schemaandimplementationrubric_8867_.pdf

Unformatted Attachment Preview

Conference Room Scheduler
Project
Hood College, CSIT 430/530
Spring 2009
Group Members
Carol Jim
Earl Davis
David Blowe
Conference Room Scheduler
April 23, 2009
RELATIONAL SCHEMA AND EXPLANATIONS
1.1
ATTENDEE
Attendee(attendeeId: INTEGER, firstName: VARCHAR(20), lastName: VARCHAR(20),
email: VARCHAR(50), phoneNumber: VARCHAR(20), scheduleId: INTEGER)
The Attendee table stores information about each attendee, such as their first name, last name,
and contact information. The attendee’s ID is the primary key for the Attendee table.
1.2
SCHEDULE
Schedule(scheduleId: INTEGER, time: TIME, day: DATE, slength: INTEGER)
The Schedule table contains the ID for the schedule as a primary key. The other information
important for the schedule table is the date and time attributes, as well as the slength attribute
that basically describes the period of time that the attendee is available in days.
1.3
MEETING
Meeting(meetingId: INTEGER, time: TIME, day: DATE, duration: INTEGER, topic:
VARCHAR(50), numAccepted: INTEGER, numInvited: INTEGER, resources:
VARCHAR(255), attendeeId: INTEGER, roomId: INTEGER)
The Meeting table contains date and time information for the meeting, as well as the intended
duration of the meeting in minutes, and topic of the meeting. Other attributes are the number of
people invited to the meeting, number of people accepted to the meeting, resources needed, and
the Ids of the room of the meeting and of the attendees of the meeting. Each meeting will have
a meeting ID as the primary key.
1.4
ATTENDS
Attends(meetingId: INTEGER, attendeeId: INTEGER)
The Attends relation has the ID of the meeting and of Attendees, both as primary keys.
1.5
ROOM
Room(roomId: INTEGER, building:
maxOccupants: INTEGER)
VARCHAR(100),
roomNumber:
INTEGER,
The Room table will contain the room ID as the primary key, the building in which the room is
in, the number of the room, and how many people the room can hold as attributes.
Page 2 of 7
Conference Room Scheduler
1.6
RESERVATION
April 23, 2009
Reservation(reservationId: INTEGER, time: TIME, day: DATE, rlength: INTEGER, roomId:
INTEGER)
Finally, the Reservation table will contain the reservation ID as the primary key, the
information of when the reservation is, such as time and date, and also the length of the
reservation will be included to quantify how long the reservation is in minutes. The room ID is
in the Reservation table because each reservation will be using a particular room.
IMPLEMENTATION OF THE SCHEMA IN MYSQL
use cjim;
/* Database Table Creation */
/* Drop any existing tables. Any errors are ignored. */
DROP TABLE IF EXISTS attends;
DROP TABLE IF EXISTS meeting;
DROP TABLE IF EXISTS reservation;
DROP TABLE IF EXISTS attendee;
DROP TABLE IF EXISTS schedule;
DROP TABLE IF EXISTS room;
/* Add each table. */
create table schedule(
scheduleId INTEGER,
time TIME,
day DATE,
slength INTEGER,
primary key (scheduleId)
)ENGINE=InnoDB;
Page 3 of 7
Conference Room Scheduler
create table room(
roomId INTEGER,
building VARCHAR(100),
roomNumber INTEGER,
maxOccupants INTEGER,
primary key (roomId)
)ENGINE=InnoDB;
April 23, 2009
/* scheduleId must have a not null constraint because each attendee must have
a schedule (total participation). Deletion of a schedule is rejected since
each attendee must have a schedule. If a schedule is updated, we want to
also update the data in the attendee table. */
create table attendee(
attendeeId INTEGER,
firstName VARCHAR(20),
lastName VARCHAR(20),
email VARCHAR(50),
phoneNumber VARCHAR(20),
scheduleId INTEGER NOT NULL,
primary key (attendeeId),
foreign key(scheduleId) references schedule(scheduleId)
ON DELETE NO ACTION
ON UPDATE CASCADE
)ENGINE=InnoDB;
/* reservationId must have a not null constraint because each reservation must
occur in a room (total participation). Deletion of a room is rejected
since each reservation must occur in a room. If room information is
updated, we also want to update the data in the reservation table. */
create table reservation(
reservationId INTEGER,
time TIME,
day DATE,
rlength INTEGER,
roomId INTEGER NOT NULL,
primary key (reservationId),
foreign key(roomId) references room(roomId)
ON DELETE NO ACTION
ON UPDATE CASCADE
)ENGINE=InnoDB;
/* attendeeId must have a not null constraint because each meeting must have
a coordinator to organize the meeting (total participation). Also, roomId
must have a not null constraint because each meeting must occur in a room
(total participation). If an attendee is deleted, we want to also delete
the meeting that the corresponding attendee is coordinating since each
meeting must have a coordinator. If attendee information is updated, we
want to update the data in the meeting table as well. Deletion of a room
Page 4 of 7
Conference Room Scheduler
is rejected since each meeting must occur in a room. If room information
is updated, we also want to update the data in the meeting table. */
create table meeting(
meetingId INTEGER,
time TIME,
day DATE,
duration INTEGER,
topic VARCHAR(50),
numAccepted INTEGER,
numInvited INTEGER,
resources VARCHAR(255),
attendeeId INTEGER NOT NULL,
roomId INTEGER NOT NULL,
primary key (meetingId),
foreign key(attendeeId) references attendee(attendeeId)
ON DELETE CASCADE
ON UPDATE CASCADE,
foreign key(roomId) references room(roomId)
ON DELETE NO ACTION
ON UPDATE CASCADE
)ENGINE=InnoDB;
/* If a meeting is deleted, we want to also delete all tuples in the
attends relation that refer to the meeting that was deleted. If meeting
information is updated, we want to also update the data in the attends
table. If an attendee is deleted, we want to also delete all tuples in
the attends relation that refer to the attendee that was deleted. If
attendee information is updated, we want to update the data in the attends
table as well. */
create table attends(
meetingId INTEGER,
attendeeId INTEGER,
primary key (meetingId, attendeeId),
foreign key(meetingId) references meeting(meetingId)
ON DELETE CASCADE
ON UPDATE CASCADE,
foreign key(attendeeId) references attendee(attendeeId)
ON DELETE CASCADE
ON UPDATE CASCADE
)ENGINE=InnoDB;
Page 5 of 7
April 23, 2009
Conference Room Scheduler
LOADING DATA
April 23, 2009
Schedule
101,09:00:00,2009-05-24,8
102,03:00:00,2009-05-20,14
103,04:00:00,2009-05-25,3
104,10:00:00,2009-05-25,7
105,08:00:00,2009-05-23,6
106,07:00:00,2009-05-24,3
107,09:00:00,2009-05-24,3
108,09:00:00,2009-05-23,5
109,11:00:00,2009-05-20,10
110,11:00:00,2009-05-25,9
Room
301,building 1,234,5
302,building 1,235,6
303,building 1,256,7
304,building 2,22,8
305,building 3,23,4
306,building 3,25,6
307,building 3,26,5
308,building 4,259,5
Attendee
1,Bob,Jones,jones@hotmail.com,3015678484,101
2,Bill,Smith,smith@aol.com,3015647681,102
3,Nancy,Delaney,delaney@hotmail.com,3013426556,103
4,Mark,Adongo,adongo@gmail.com,3016983214,104
5,Kelly,Johnson,johnson@aol.com,3011436587,105
6,Janis,Miles,miles@hotmail.com,3010987564,106
7,Joe,Raik,raik@hotmail.com,3017584736,107
8,Maria,Vargas,vargas@hotmail.com,3019398276,108
9,Sarah,Marshall,marshall@gmail.com,3016631012,109
10,Tom,Cohen,cohen@gmail.com,3018783204,110
Reservation
401,12:30:00,2009-05-25,30,301
402,03:00:00,2009-05-25,30,308
403,07:30:00,2009-05-26,120,303
404,10:00:00,2009-05-26,90,302
405,02:00:00,2009-05-26,60,304
406,01:30:00,2009-05-27,60,307
407,08:30:00,2009-04-27,60,303
408,11:30:00,2009-05-30,60,306
409,08:00:00,2009-05-25,30,301
410,04:00:00,2009-05-27,30,305
Page 6 of 7
Conference Room Scheduler
April 23, 2009
Meeting
201,12:30:00,2009-05-25,30,Budget,3,3,Projector and Laptop,1,301
202,02:00:00,2009-05-26,60,Project Deadlines,6,6,None,2,304
203,01:30:00,2009-05-27,45,Marketing,4,5,None,3,307
204,03:00:00,2009-05-25,20,Information Technology,3,3,Projector and Laptop,4,308
205,08:00:00,2009-05-25,25,Security,3,5,None,5,301
206,08:30:00,2009-04-27,40,Sales,3,3,None,6,303
207,10:00:00,2009-05-26,80,New Project Ideas,3,3,None,7,302
208,07:30:00,2009-05-26,110,Quarterly Progress Report,4,4,Projector,8,303
209,04:00:00,2009-05-27,25,Recruiting,3,4,None,9,305
210,11:30:00,2009-05-30,60,Employee Review,3,3,None,10,306
Attends
201,1
201,2
201,6
202,2
202,5
202,6
202,7
202,9
202,10
203,3
203,1
203,4
203,8
204,4
204,1
204,7
205,5
205,2
205,8
206,6
206,2
206,5
207,7
207,3
207,5
208,8
208,4
208,7
208,10
209,9
209,2
209,3
210,10
210,1
210,4
Page 7 of 7
Applied Database Systems
Relation Schema and Its Implementation
1. Revise your design, if necessary.
2. Translate your ER diagram into Relational Schema
Write up a relational schema that is equivalent to your ER diagram. Make
sure that you translate not only entities but also relationships. Write a short
explanation for each relation. For example,
Orders
Orders(ordernum: INTEGER, cid: INTEGER,
cardnum: CHAR(16),
cardmonth: INTEGER, cardyear: INTEGER, order_date: DATE,
ship_date: DATE)
This table stores one entry for each order the user places.
It
only includes general information about the transaction itself, and
not the specific items purchased by the user.
The cid of the user
placing the order as well as their credit card number and credit
card date are stored here.
In addition, we can track when the order
was submitted as well as when it shipped.
3. Implementation of the Schema in MariaDB
Write an SQL script with the commands to create the tables of your database.
The CREATE TABLE statements must specify: o
Appropriate types for the attributes;
o The primary key; o Constraints such as NOT NULL and UNIQUE
whenever appropriate; o Default values where appropriate;
o FOREIGN KEY constraints, together with the policy for reacting to
changes (remember that the default is ON DELETE NO ACTION)
Write comments into the script that explain the rationale behind the definition of your
constraints.
4. Loading Data
Load interesting data into the database. In principle, you can do this either
by writing many INSERT statements, or by creating a file that contains the
data that you want to fill into a relation and to load the file content using the
Applied Database Systems IT 530: Project
bulk loader as we did in Homework 4. Eac h relatio n sho uld have at least
8 rec o rds.
Submission
Compile the following into one file and submit it through Blackboard. o
The relational schema and explanations;
o An SQL script file that creates the tables of your database and contains comments
on the constraints, etc. you have chosen;
o For each table, a script with SQL insert statements or a loader file with data. If
you have a very large data file, only submit the first two pages of data. Don’t
crash Blackboard!
Relation Schema and SQL Implementation Grading Rubric
Applied Database Systems
5
Exemplary
3
Satisfactory
Relation schemas and
SQL code capture all
entity sets and
relationships from the
team’s E-R diagram and
account for suggested
revision to that diagram. A
convincing explanation
is included for any
suggested revisions not
accepted or for deviations
from the original design. A
complete explanation for
each relation is included.
Relation schemas and
SQL code include all
attributes from the team’s
E-R diagram and all
additional attributes
necessary to represent
any relationships from
that diagram.
Relation schemas and
SQL code capture most
entity sets and
relationships from the
team’s E-R diagram
and account for most
suggested revisions to
that diagram. An
explanation for each
relation is included for
some relations but not
all, or the explanations
are incomplete.
Criteria
(Weight)
Relations
(x3)
Attributes
(x2)
Primary Keys
(x2)
Foreign Keys
(x2)
Constraints (x1)
Relation schemas and
SQL code indicate a
primary key for every
relation. All primary keys
are clearly unique, or
explanations are offered
for their uniqueness.
SQL code indicates all
foreign keys and shows
the tables that they
reference.
SQL code captures all
cardinality and
participation constraints
necessary for a database
that would satisfy the initial
design requirements.
Detailed comments for
each table explain
rationale behind the
constraints.
Other comments:
1
Needs
Improvement
Relation schemas and
SQL code capture few, if
any, entity sets and
relationships from the
team’s E-R diagram.
Document does not
account for suggested
revisions to that diagram.
Explanations for each
relation are not to be
found.
Relation schemas and
SQL code include all
attributes from the
team’s E-R diagram
and most additional
attributes necessary to
represent any
relationships from that
diagram.
Relation schemas and
SQL code indicate a
primary key for nearly
all relations. Most
primary keys are clearly
unique.
Relation schemas and
SQL code include few, if
any, attributes from the
team’s E-R diagram.
No attributes necessary
to represent relationships
are included.
SQL code indicates
most foreign keys and
shows the tables that
they reference.
SQL code captures
most of the cardinality
and participation
constraints necessary
for a database that
would satisfy the initial
design requirements.
Comments are written
for some tables, or are
not complete.
SQL code does not
indicate foreign keys.
Relation schemas and
SQL code fail to indicate
primary keys.
SQL code captures none
or few of the cardinality
and participation
constraints necessary for
a database that would
satisfy the initial design
requirements. There are
no comments for any of
the tables.
Score
(Weighted)

Purchase answer to see full
attachment

error: Content is protected !!