Download DATA BASE LAB SQL SERVER ALONG WITH ENTITY DIAGRAMS and more Lab Reports Database Management Systems (DBMS) in PDF only on Docsity!
S No List of Experiments
1 EXPERIMENT 1 – CONCEPTUAL DATABASE DESIGN USING ENTITY RELATIONSHIP
DIAGRAMS
2 EXPERIMENT 2 – DEVELOP DATA MODELS FOR BUSINESS MODELS
3 EXPERIMENT 3 – Data Definition Languages
4 EXPERIMENT 4 – DATA MANIPULATION LANGUAGE (DML)
5 EXPERIMENT 5 – Group by and Having
6 EXPERIMENT 6 – SQL INBUILT FUNCTIONS
7 EXPERIMENT 7 – Nested Queries
8 EXPERIMENT 8 – JOIN INTRODUCTION
9 EXPERIMENT 9 – JOIN Inner/Outer/Full/Self
10 EXPERIMENT 10 – STORED PROCEDURES/CONTROL STRUCTURES
11 EXPERIMENT 11 – TRIGGERS
12 EXPERIMENT 12 – CURSORS
13 EXPERIMENT 13 – FRONT END TOOL I
14 EXPERIMENT 14– FRONT END TOOL II
SAMPLE DATABASE SCHEMAS For Practice: Order Entry Database Background
The Order Entry database is used for extra problems/pracice. This document describes the tables and relationships.
Table Description and Usage: -
The Order Entry database is an extension of the order entry tables used in the extra problems/practice. Table 1 lists the
meaning of each table and Figure 1 shows the database diagram. After the relationship diagram, sample rows and
CREATE TABLE statements are shown for each table. In addition to the other documentation, here are some notes about
the Order Entry Database:
● The primary key of the OrdLine table is a combination of OrdNo and ProdNo.
● The Employee table has a self-referencing (unary) relationship to itself through the foreign key, SupEmpNo , the
employee number of the supervising employee. In the relationship diagram, the table Employee_1 is a
representation of the self-referencing relationship, not a real table.
Table 1: Tables of the Order Entry Database
Table Name Description Customer List of customers who have placed orders OrderTbl Contains the heading part of an order; Internet orders do not have an employee Employee List of employees who can take orders OrdLine Contains the product detail parts of orders Product List of products that may be ordered
Figure 1: Database Diagram for the Order Entry Database
OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity OrdStat e OrdZip O 4 01/23/ 17 C 7 E 9 Sheri Gordon 336 Hill St. Littleton CO 80129- O 1 01/23/ 17 C 0 E 2 Larry Styles 9825 S. Crest Lane Bellevue WA 98104- O 8 02/10/ 17 C 2 Todd Hayes 1400 NW 88th Lynnwood WA 98036- O 2 01/09/ 17 C 2 E 1 Wally Jones 411 Webber Ave. Seattle WA 98105- O 9 01/05/ 17 C 9 E 9 Tom Johnson 1632 Ocean Dr. Des Moines WA 98222- O 1 01/23/ 17 C 0 E 9 Candy Kendall 456 Pine St. Seattle WA 98105- O 7 02/11/ 17 C 1 Ron Thompson 789 122nd St. Renton WA 98666- O 7 01/12/ 17 C 7 E 5 Beth Taylor 2396 Rafter Rd Seattle WA 98103- O 1 01/14/ 17 C 7 E 4 Mrs. Ruth Gordon 233 S. 166th Seattle WA 98011 O 9 01/23/ 17 C 8 E 2 Mike Boren 642 Crest Ave. Englewoo d CO 80113- O 2 01/13/ 17 C 8 Jim Glussman 1432 E. Ravenna Denver CO 80111- O 3 01/15/ 17 C 4 E 1 Jerry Wyatt 16212 123rd Ct. Denver CO 80222- O 5 01/11/ 17 C 7 E 4 Beth Taylor 2396 Rafter Rd Seattle WA 98103- O 5 01/22/ 17 C 9 E 5 Betty White 4334 153rd NW Seattle WA 98178- O 6 01/20/ 17 C 4 E 1 Mr. Jack Sibley 166 E. 344th Renton WA 98006- O 2 01/23/ 17 C 1 Harry Sanders 1280 S. Hill Rd. Fife WA 98222- O 8 02/19/ 17 C 1 E 9 Ron Thompson 789 122nd St. Renton WA 98666- O 7 01/16/ 17 C 3 E 1 Bob Mann 1190 Lorraine Cir. Monroe WA 98013- O 5 01/23/ 17 C 4 HelenSibley 206 McCaffrey Renton WA 98006- O 9 02/11/ 17 C 2 E 2 Homer Wells 123 Main St. Seattle WA 98105-
Employee
EmpNo EmpFirstName EmpLastName EmpPhone EmpEMail SupEmpN o EmpCommRat e E 4 Landi Santos (303) 789- 1234 [email protected] (^) E8843211 0. E 9 Joe Jenkins (303) 221- 9875 [email protected] (^) E8843211 0. E 1 Amy Tang (303) 556- 4321 [email protected] E9884325 0. E 1 Colin White (303) 221- 4453 [email protected] E9884325 0. E 5 Thomas Johnson (303) 556- 9987 [email protected] m
E 2 Mary Hill (303) 556- 9871 [email protected] (^) E8843211 0. E 0 Theresa Beck (720) 320- 2234 [email protected] E
Product
ProdNo ProdName ProdMfg ProdQOH ProdPrice ProdNextShipDat
e Changed after
draft sent. Must
change in the
copy edits.
P
17 inch Color Monitor ColorMeg, Inc. 12 $169.00 2/20/ P 7 19 inch Color Monitor ColorMeg, Inc. 10 $319.00 2/20/ P 0 R3000 Color Laser Printer Connex 5 $699.00 1/22/ P 8 10 Foot Printer Cable Ethlite 100 $12. P 1 8-Outlet Surge Protector Intersafe 33 $14. P 8 CVP Ink Jet Color Printer Connex 8 $99.00 1/22/ P 3 Color Ink Jet Cartridge Connex 24 $38.00 1/22/
OrdLine
OrdNo ProdNo Qt y O 4
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
O
P
CONSTRAINT PKEmployee PRIMARY KEY (EmpNo), CONSTRAINT UNIQUEEMail UNIQUE(EmpEMail), CONSTRAINT FKSupEmpNo FOREIGN KEY (SupEmpNo) REFERENCES Employee ) CREATE TABLE OrderTbl ( OrdNo CHAR( 8 ), OrdDate DATE CONSTRAINT OrdDateRequired NOT NULL, CustNo CHAR( 8 ) CONSTRAINT CustNoRequired NOT NULL, EmpNo CHAR( 8 ), OrdName VARCHAR( 50 ), OrdStreet VARCHAR( 50 ), OrdCity VARCHAR( 30 ), OrdState CHAR( 2 ), OrdZip CHAR( 10 ), CONSTRAINT PKOrderTbl PRIMARY KEY (OrdNo) , CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer, CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee ) CREATE TABLE OrdLine ( OrdNo CHAR( 8 ), ProdNo CHAR( 8 ), Qty INTEGER DEFAULT 1 , CONSTRAINT PKOrdLine PRIMARY KEY (OrdNo, ProdNo), CONSTRAINT FKOrdNo FOREIGN KEY (OrdNo) REFERENCES OrderTbl ON DELETE CASCADE, CONSTRAINT FKProdNo FOREIGN KEY (ProdNo) REFERENCES Product ) CREATE TABLE Product ( ProdNo CHAR( 8 ), ProdName VARCHAR( 50 ) CONSTRAINT ProdNameRequired NOT NULL, ProdMfg VARCHAR( 20 ) CONSTRAINT ProdMfgRequired NOT NULL, ProdQOH INTEGER DEFAULT 0 , ProdPrice DECIMAL( 12 , 2 ) DEFAULT 0 , ProdNextShipDate DATE, CONSTRAINT PKProduct PRIMARY KEY (ProdNo) ) For Exercise:- Intercollegiate Athletic Database Tables
You will use the Intercollegiate Athletic database. This document describes the tables and relationships.
Table Description and Usage
The EventRequest table is the hub of the database. An event request represents an event scheduled at a facility. For
example, a basketball game may be scheduled at the gymnasium. Events are sometimes scheduled several months in
advance. Holding an event requires resources including personnel and equipment. Resources are assigned to specific
locations of a facility. For example, guards may be required at the gates of the football stadium. The EventPlan table
defines a plan for the setup, operation, and cleanup of an event. The EventPlanLine table contains the individual
resources required in an event plan. The Intercollegiate Athletic database supports the scheduling and operation of
events. Customers initiate event requests with the Intercollegiate Athletic Department. The facility and date held are
recorded on the event request. If an event request is denied, no additional action is taken. If an event request is
approved, one or more event plans are made. Typically, event plans are made for the setup, operation, and clean up of
an event. An employee is assigned to manage an event plan before the plan is executed. Initially, there may not be an
assigned employee. An event plan consists of one or more event plan lines. In an event plan line, the resource, location,
time, and number of resources ( EventPlanLine.Number ) are recorded.
Table Listings Customer custn o custname address Internal contact phone city stat e zip C100 Faisal Naseer Box 352200 Yes Mary Manager 685710 0 Boulder CO 8030 9 C101 Mubashir Nazir Box 352400 Yes Sally Supervisor 543170 0 Boulder CO 8030 9 C103 Babar Nawaz Box 352020 Yes Bill Baseball 543123 4 Boulder CO 8030 9 C104 Waheed Iqbal Box 351200 Yes Sue Softball 543432 1 Boulder CO 8030 9 C105 Hashir Shahid 123 AnyStreet No Coach Bob 444123 4 Louisville CO 8002 7 Employee empn o empname department email phon e E100 Chuck Coordinator Administratio n [email protected] 3- 1111 E101 Mary Manager Football [email protected] 5- 1111 E102 Sally Supervisor Planning [email protected] 3- 2222 E103 Alan Administrator Administratio n [email protected] 3- 3333 Facility facn o facname F 0 Football stadium F 1 Basketball arena F 2 Baseball field F 3 Recreation room Location locn o facn o locname L100 F100 Locker room L101 F100 Plaza L102 F100 Vehicle gate L103 F101 Locker room
EventPlanLine
PlanNo LineNo TimeStart TimeEnd NumberFl d EventPlan
The primary and foreign keys are depicted in Figure 1. An event request is related to many (one or more) event plans
but only one customer. An event plan contains many event plan lines but only one supervising employee. An event plan
line references a resource and location. A facility has many locations, but a location is specific to a facility.
Figure 1: Oracle Relational Database Diagram for the Intercollegiate Athletic Database
All foreign key columns are required except for EventPlan.EmpNo. When a column is required, the user must enter a
valid value according to the specified integrity rules (including referential integrity). For example when entering a new
row in the EventRequest table, the user must know the customer number.
EXPERIMENT 1 – CONCEPTUAL DATABASE DESIGN USING ENTITY RELATIONSHIP DIAGRAMS
OBJECTIVE
Design ERD’s Using Crow’s Foot notations in ER Assistant.
THEORY
The entity relationship diagram (ERD) is a graphical representation that depicts things of interest (entities) and
relationship among entities. ERD’s have three basic elements: entity types, relationships and attributes.
Entity: Entities are collection of things of interest in an application. Each entity type represents collections of
physical things such as books, people and places as well as events such as payments.
Attribute: attributes are properties of entity types and relationships.
Relationships: are named associations among entity types.
ERD Basic Symbols:
Relationship Cardinality: cardinalities constrain the number of entities that participate in a relationship.
Classification of Cardinalities:
16 CS 220 Database System
Weak entities and Identifying relationships: Entity types that borrow part or their entire primary key are
known as weak entities. The relationship that provides components of the primary key is known as an
identifying relationship.
Self-Referencing Relationship: A self-referencing relationship involves connections among members of the
same entity type.
17 CS 220 Database System
Practice Problems for Experiment 1:-
The practice problem using the Crow’s Foot notation. You are encouraged to use the ER Assistant or other
drawing tool to complete the practice problems in Experiment.
1. Draw an ERD containing Student and Paper entity types connected by a 1-M relationship. The Student
entity type should have attributes for StdNo (primary key), StdFirstName , StdLastName , StdAdmitSemester ,
StdAdmitYear , and StdEnrollStatus (full or part-time). The Paper entity type should have attributes for
PaperNo (primary key), PaperTitle , PaperSubmitDate , PaperAccepted (yes or no), and PaperType (first,
second, proposal, or dissertation). Add a 1-M relationship from Student to Paper.
2. Extend the ERD with an Evaluator entity type and an M-N relationship between Paper and Evaluator. The
Evaluator entity type should have attributes for EvalNo (primary key), EvalFirstName , EvalLastName ,
EvalEmail , and EvalOffice. The M-N relationship should have attributes for EvalDate , EvalLitReview (1 to
5 rating), EvalProbId (1 to 5 rating), EvalTechWriting (1 to 5 rating), EvalModelDev (1 to 5 rating),
EvalOverall (1 to 5 rating), and EvalComments.
3. Transform the M-N relationship from problem 9 into an associative entity type and identifying relationships.
Solution:
Part 1
Part 2
19 CS 220 Database System
Part 3
Summary
Student will understand attributes and relationship types used during ER Modeling.
Web Resources for Additional Studies
https://www.youtube.com/watch?v=a6UCXWJZ2GE
https://www.youtube.com/watch?v=bivWAcqVGrs
https://datapandas.com/index.php/2017/02/18/how-to-use-er-assistant-free-entity-relationship-diagram-erd-
creation-tool/
20 CS 220 Database System
