Idea Transcript
Jason Thai Alexzander Avila CMPS 342
1
Fast Food Restaurant Database CMPS 342, Fall 2015
Jason Thai Alexzander Avila CMPS 342
2
Table of Contents Phase I .............................................................................................. 4 1.1 Fact-‐Finding Techniques and Information Gathering ......... 4 1.1.1 Introduction to Enterprise/Organization ...................................................... 4 1.1.2 Description Fact-‐Finding Techniques ............................................................. 4 1.1.3 The Part of Enterprise we are designing Conceptual Database for .... 5 1.1.4 Itemized Descriptions of Entity Sets and Relationship Sets .................. 6 1.1.5 User Groups, Data Views and Operations ...................................................... 8
1.2 Conceptual Database Design ........................................................ 9 1.2.1 Entity Set Description ............................................................................................. 9 1.2.2 Relationship Set Description .............................................................................. 16 1.2.3 Related Entity Set .................................................................................................. 19 1.2.4 E-‐R Diagram ............................................................................................................. 20
Phase II .......................................................................................... 21 2.1 E-‐R Model and Relational Model .............................................. 21 2.1.1 Description of E-‐R model and Relational Model ...................................... 21
Jason Thai Alexzander Avila 3 CMPS 342 2.1.2 Comparison of Two Different Models .......................................................... 21
2.2 Conceptual Database, Logical Database, and Conversion From ER to Relational Database ...................................................... 22 2.2.1 Converting Entity Types to Relations ........................................................... 22 2.2.2 Converting Relationship Types to Relations ............................................. 23 2.2.3 Database Constraints ........................................................................................... 25
2.3 Convert Your E-‐R Database into a Relational Database ... 26 2.3.1 Relational Schema For The Database ........................................................... 27 2.3.2 Sample Data of Relation ..................................................................................... 32
2.4 Sample Queries to Our Database .............................................. 38 2.4.1 Design of Queries .................................................................................................. 38 2.4.2 Relational Expressions for Queries: .............................................................. 39
Jason Thai Alexzander Avila CMPS 342
4
Phase I
1.1 Fact-‐Finding Techniques and Information Gathering 1.1.1
Introduction to Enterprise/Organization A fast food restaurant manages serving food to their customers and keeping track of
their inventory to maximize profit. Each employee is assigned a different position in order to work together to provide the orders in the least amount of time possible. There must also be a supplier to provide the necessary ingredients to prepare the customers’ orders.
1.1.2
Description Fact-‐Finding Techniques Most of our information comes from personal experience. However, reading from
our database book gives similar examples from which we were able to get a general idea of how a fast food restaurant looks like from a conceptual point of view. Working in Fast Food for over 5 years, I've gained plenty of experience in Customer Service and Sales. While I've never obtained the position of Manager (Assistant or Store), I have observed and been taught how to log sales reports for the company. From my own experience, Sales are recorded into the POS system and, at the end of the night, a large receipt that contains information on the overall purchase history for the day is printed and given to the Store Owner.
Jason Thai Alexzander Avila 5 CMPS 342 For our data collection, we will view Sales Reports, mentioned previously, and log them ourselves in our own database. I will also, personally, interview members of the company to obtain data that the Sales Reports do not give.
Operations on Data
The sole user of the Sales Data will be entered and used by the franchise
owner/store owner. He/She will develop reports of what was sold, how much was sold, what sold well, and what did not sell well. That report will be sent to the company headquarters for logging and generating instructions for the next report. Along with the report, the store owner will also use the Sales Data to log the inventory used to produce the amount of sales and will contact the Supplier to order more inventory.
1.1.3
The Part of Enterprise we are designing Conceptual Database for A fast food restaurant is usually part of a chain of fast food restaurants. The chain is
normally a way for the restaurants to keep in touch for any change in their menu such as prices and limited time offers. However, creating such a database for a fast food restaurant chain may prove difficult as our personal experience doesn’t go beyond to managing large scale businesses. We’ve agreed it would in our favor to design a conceptual database for a general fast food restaurant itself only. With our focus centered on the Sales portion of a Fast Food Restaurant, we've narrowed the number of entities to the most basic forms. Therefore, we don't have the Employee entity shown with disjointness; we don't have much information on the Customer Entity; and most of the data collection is derived from the Order and Item Entity.
Jason Thai Alexzander Avila 6 CMPS 342 We may, however, include an entity that connects the Customer and “Supervises” entity and relationship, which will describe a new form of data that we'll call Performance. This data will be used to log employee performance for the Supervisor.
Major Entity Set and Relationship Sets
The employee entity is one of the three major entities of this database. Basic
information is included within this entity while it branches off to (disjoint/overlapping?) subclasses for the different occupations within a fast food restaurant. The order entity is the second of the three major entities. This entity will contain information about what a customer wants from the employees. The item entity is the third of the three major entities. Information pertaining to price, what it consists of, etc. is stored in this entity. Because fast food restaurants have combos, it is possible for the item to have the relationship to consist of itself. Also, a supplier entity shows what and how much is supplied. (relationship ….)
1.1.4
Itemized Descriptions of Entity Sets and Relationship Sets The employee entity is a set of employees each with some unique attributes that
distinguish themselves from other employees. This entity consists of: name, address, salary, gender, birthday, social security number, start/end date, and availability. The “supervises” relationship shows the action between employees. Its cardinality is one supervisor to many supervisees.
Jason Thai Alexzander Avila 7 CMPS 342 The “fills” relationship shows that the employee (cashier) filling out the order. Its cardinality is one employee to many orders. The order entity is an entity set that an employee must fulfill for a customer. This entity consists of: total cost, order id, list of items, and date. The “takes” relationship shows the customer asking for the order. Its cardinality is one customer to one order. The customer entity is the set of customers that comes to the fast food restaurants. This entity consists of: name and order number. The “contains” relationship shows what items the order contains. Its cardinality is one order to many items. The item entity set consists of: price, ingredients, allergens, and nutritional info. The “consists of” relationship shows what a combo meal may have. Its cardinality is one item to many items. The supplier entity set consists of: delivery date, prices, and invoice number. The “supplies” relationship shows what items the supplier gives. Its cardinality is one supplier to many items. The “fills out” relationship describes the physical action the customer takes to submit a survey of their visit. Its cardinality is one customer to one survey. The “mentions” relationship describes the connection of the survey to the employee such that the customer had mentioned the employee, or employees, in their survey. Its cardinality is one survey to either one or many employees.
Jason Thai Alexzander Avila CMPS 342
1.1.5
User Groups, Data Views and Operations
*save for final phase*
8
Jason Thai Alexzander Avila CMPS 342
9
1.2 Conceptual Database Design 1.2.1
Entity Set Description
Employee Description: Stores basic information pertaining to each individual employee, such as his or her name and salary. A new entry is inserted only if a new employee is hired and should never be deleted. It’s possible that an entry can be updated such as an employee’s address. An employee can supervise one or more employees. Also, one employee can fill one or more orders. Candidate keys: 1.
Employee Id (primary key)
2.
Name
This is a strong entity. Fields to be indexed: 1.
Employee Id
2.
Name
Attribute Name
Name
Address
Salary
Gender
Birthday
SSN
Availability
Employee Id
Description
First, Last,
Street
Amount
Male or
Date of
9-‐digit
Time of
Id
Middle
address,
paid
Female
birth
number
available
column
name
City, State,
hourly
hours in a
for
week
internal
ZIP code
Jason Thai Alexzander Avila CMPS 342
10 use
Domain/Type
Value-‐Range
Full Name
Address (4
Money
M or F
Date (3
9-‐digit
Days, Hours
(3 strings)
strings)
(double)
(character)
integer)
integer
(integer)
Any
Any
Above
M or F
Range of
9 digits
Range of date
0 to Max
type
Integer
None
Max Id +
$0 Default Value
None
None
$9/hour
date type None
None
None
Integer
1 Nullable
Yes
Yes (2nd
(middle
street
name
address
only)
only)
Unique
Yes
Single/Multiple-‐Value Simple/Composite
No
No
No
No
No
No
No
No
No
No
Yes
No
Yes
Single
Single
Single
Single
Single
Single
Multiple
Single
Composite
Composite
Simple
Simple
Composite
Simple
Composite
Simple
Jason Thai Alexzander Avila CMPS 342
11
Order
Description: Stores information on what the customer wants. Entries are inserted as each customer comes in, and shouldn’t be deleted. Updates can sometimes be frequent should a customer change their order before transaction. One employee can fill many orders, one customer can take one order, and an order can contain many items. Candidate keys: 1.
Order Id (primary key)
This is a weak entity. Fields to be indexed: 1.
Order Id
Attribute Name
Total Cost
Order Id
List of Items
Date
Description
Amount of money to
Id column of internal
List of item entities
Time and date of
receive order
use
within order
order
Domain/Type
Money (double)
Integer
String
Integer
Value-‐Range
Above $0.00
0 to Max Integer
Any
Range of date type
Default Value
$0.00
Max Id + 1
None
None
Nullable
No
No
No
No
Unique
No
Yes
No
Yes
Single/Multiple-‐value
Single
Single
Single
Single
Simple/Composite
Simple
Simple
Composite
Composite
Jason Thai Alexzander Avila CMPS 342
12
Customer
Description: Keeps track of who ordered what by using their name and/or order number. An entry is inserted whenever the customers orders something, and can be deleted once the transaction is complete or the order is cancelled. Updates aren’t possible for this entity. Candidate keys: 1.
Order number (primary key)
2.
Name
This is a strong entity. Fields to be indexed: 1.
Order number
Attribute Name
Name
Order number
Description
First name
Number to indicate customer
Domain/Type
String
Integer
Value-‐Range
Any
0 to Max Integer
Default Value
None
Max number + 1
Nullable
Yes
No
Unique
No
Yes
Single/Multiple-‐value
Single
Single
Simple/Composite
Simple
Simple
Jason Thai Alexzander Avila CMPS 342
13
Item
Description: Food information that is part of an order. If the item is a combo meal, then the item may consists of itself. An entry is inserted if there is a new item to offer, and should be deleted if it’s a limited time offer. Updates may be frequent whenever the price of an item goes up or down. Candidate keys: 1.
Name (primary key)
This is a weak entity. Fields to be indexed: 1.
Name
Attribute Name
Name
Price
Ingredients
Allergens
Nutritional Info
Description
Name of the
Amount of money
Food/substance
Cautionary info to
Detailed info about
item
per item.
needed to make the
warn customers of
nutrients within
item.
potential allergic
the item.
reaction. Domain/Type
String
Money (double)
String
String
Name (string), Amount (double)
Value-‐Range
Any
Above $0.00
Any
Any
Any
Default Value
None
$0.00
None
None
None
Nullable
No
No
No
Yes
No
Unique
Yes
No
Yes
No
Yes
Single/Multiple-‐value
Single
Single
Multiple
Multiple
Multple
Simple/Composite
Simple
Simple
Simple
Simple
Composite
Jason Thai Alexzander Avila CMPS 342
14
Supplier
Description: Either one person or a group of people help to supply the items to the fast food restaurant. An entry is inserted whenever a new batch of shipment comes in, and the entry should never be deleted. Updates aren’t possible. Candidate keys: 1.
Invoice number (primary key)
2.
Delivery date
This is a strong entity. Fields to be indexed: 1.
Invoice number
2.
Delivery date
Attribute Name
Delivery date
Price
Invoice number
Description
Time and date of supplies
Amount of money needed to
Tracking number for the
arriving.
supply items.
supplying company.
Domain/Type
Month/day/year (3 integers)
Money (double)
String
Value-‐Range
Range of date type
Above $0.00
0 to Max Integer
Default Value
None
$0.00
Max invoice number + 1
Nullable
No
No
No
Unique
No
No
Yes
Single/Multiple-‐value
Single
Single
Single
Simple/Composite
Composite
Simple
Simple
Jason Thai Alexzander Avila CMPS 342
15
Survey Description: This entity plays the role of a different form of data collection. The purpose of including this entity is that Customers will submit surveys that tell of Employee Performance, quality of their order, and overall Customer Satisfaction. An entry is inserted when a customer submits a survey. Candidate keys: 1.
Employee Performance
2.
Customer Satisfaction
3.
Date/Time
This is a strong entity. Fields to be indexed: 1.
Employee Performance
2.
Customer Satisfaction
Attribute Name
Employee Performance
Customer Satisfaction
Date/Time
Description
A numerical measurement of
A numerical measurement of
Time and Date of order
an employee’s overall
how satisfied a customer was.
placement.
performance. Domain/Type
String
String
Month/Day/Year (3 integers)
Value-‐Range
0 to Max Integer
0 to Max Integer
Range of date type
Default Value
None
None
None
Nullable
Yes
No
No
Unique
Yes
No
Yes
Single/Multiple-‐value
Single
Single
Single
Simple/Composite
Composite
Simple
Composite
Jason Thai Alexzander Avila CMPS 342
16
1.2.2 Relationship Set Description
Supervises Description: One employee may be able to be obligated to supervise the other many employees. This recursive relationship only has one entity since it loops back to the employee entity. The start/end time describes the time an employee starts supervising and ends. Entity sets involved: Employee Mapping Cardinality: 1..N Descriptive Fields: Start/end time Participation Constraint: Partial for the supervisor but total for the supervisee. Not all employees will supervise.
Fills Description: An employee is assigned to fill out the many orders of the customers. This is a binary relationship between the employee and the order. It contains the date/time and employee attributes that describes when and who fills out the order, which would make an appearance on the receipt of the order. Entity sets involved: Employee, Order Mapping Cardinality: 1..N Descriptive Fields: Date/time, Employee
Jason Thai Alexzander Avila 17 CMPS 342 Participation Constraint: Partial for employee but total for the orders. Not all employees can fill out the orders but someone must fill out all the orders.
Takes Description: A binary relationship of one customer taking one order. The date attribute helps keep track of when the order is taken. Entity sets involved: Customer, Order Mapping Cardinality: 1..1 Descriptive Fields: Date Participation Constraint: Total, since one order correlates to one customer.
Contains Description: One order will contain at least one item. This is a binary relationship between order and item. Entity sets involved: Order, Item Mapping Cardinality: 1..N Descriptive Fields: None Participation Constraint: Total, since order must contain at least one item for the employee to know what to make and the supplier to know what to supply.
Consists Of
Jason Thai Alexzander Avila 18 CMPS 342 Description: If the item is a combo meal, the item will consist of itself since a combo meal has multiple items, hence this is a recursive relationship. The deals, combos, and limited time offer attributes describe what kind of item is being included. Entity sets involved: Item Mapping Cardinality: 1..N Descriptive Fields: Deals, Combos, Limited time offer Participation Constraint: Partial, since not all items are combo meals.
Supplies Description: A supplier provides the item necessary for a fast food restaurant to start cooking and fill out orders. This is a binary relationship between supplier and item. The quantity, order id, list of items, and date attributes describe what, when, how many items is being supplied. Entity sets involved: Supplier, Item Mapping Cardinality: 1..N Descriptive Fields: Quantity, Order id, List of items, Date Participation Constraint: Total, since all suppliers have to supply the necessary items.
Fills Out Description: A customer physically fills out a survey of their visit to the fast food restaurant (whether in person or online). This is a binary relationship that connects the Customer and Survey entities. The Employee Performance, Customer Satisfaction, and Date/Time fields describe the type of data collected.
Jason Thai Alexzander Avila CMPS 342 Entity sets involved: Customer, Survey
19
Mapping Cardinality: 1..1 Descriptive Fields: Employee Performance, Customer Satisfaction, Date/Time Participant Constraint: Optional; considering not all customers have to fill out a Survey of their visit.
Mentions Description: A customer fills out a Survey of their visit to the fast food restaurant. They have the option of giving feedback on the employee, or employees that helped them during their visit. This data collection helps higher up officials either compensate or reprimand their employee based on the response of the Customer. Entity sets involved: Survey, Employee Mapping Cardinality: 1..N Descriptive Fields: None Participant Constraint: Optional; when a customer fills out a survey, the option to give feedback on a certain employee, or employees, is completely optional to the customer.
1.2.3 Related Entity Set
This database doesn’t have any super classes or subclasses. Instead, it consists of
recursive tables.
Jason Thai Alexzander Avila CMPS 342
20
1.2.4 E-‐R Diagram
Jason Thai Alexzander Avila CMPS 342
21
Phase II
2.1 E-‐R Model and Relational Model
2.1.1 Description of E-‐R model and Relational Model
Peter Chen created the entity-‐relationship model in 1976. It’s a data model that
describes informational aspects of a business. The main components consist of entities and the relationships between them. This is done in an abstract way that allows it to be used in a database.
In 1969, Edgar F. Codd described the relational model as a database to managing
data. Their main component is representing data as tuples grouped into relations. The purpose of relational database is to provide a method to specify data and queries.
2.1.2 Comparison of Two Different Models
The E-‐R model is a conceptual model that gives a general overview of the company
and can be understood by most users. It provides accurate descriptions of the entities and their relationships. However, it doesn’t have any implementation detail and no associated query language.
The relational model is a logical model that allows the user to state what kind of
information is stored and what information they want from the database. It doesn’t provide as much accuracy of the entities and their relationships; however, it provides more accuracy for any given relational database than a conceptual model. This model is usually
Jason Thai Alexzander Avila 22 CMPS 342 only use by database managers, someone with SQL experience. The relational model does have an associated query language.
2.2 Conceptual Database, Logical Database, and Conversion From ER to Relational Database
A conceptual database is a general, yet high-‐level description, of a business’s
informational needs. This only includes the main components such as entities and their relationships. It’s usually the first step before making the actual database. This database doesn’t show the internal details/information of the database.
A logical database is a digital database that focuses on organizing the data into
tables. These tables (or relations) consist of rows, columns, and a unique key for each row. The tables may also contain foreign keys if they are related to another table. This database requires using SQL (structured query language) to access and maintain the database.
There’s a need for translation because they both cover each other’s faults. A
conceptual database gives a general accurate map of the company, but it doesn’t have any control on the actual database. A logical database allows querying and maintaining the database, but it lacks an accurate layout of the entities and relationships.
In this section, we’ll be explaining how to convert entity types to relations,
relationship types to relations, and database constraints.
2.2.1 Converting Entity Types to Relations
To convert strong entities to relations, both simple and composite attributes should
be included. Any attributes that form a key can become the primary key for the entity. Any other candidate keys may be kept for indexing purposes.
Jason Thai Alexzander Avila 23 CMPS 342 Converting weak entities to relations is similar, as both simple and composite attributes should be included. However, it should also include the primary key of its owner entity (a strong entity) and be represented as both a foreign key and primary key. These foreign keys help to handle and maintain any information that goes through both the weak and its owner entity.
2.2.2 Converting Relationship Types to Relations There are 3 ways to convert binary 1:1 relationships: 1.
Foreign Key Method: Include the primary key of the relation with the least participation into the other as a foreign key. This method should be used if at least one of the relations has total participation. Having the foreign key in the relation with most/total participation will avoid the issue of having nulls.
2.
Merged Relation: If both relations have total participation, they may merge together to form a single relation. This method is not recommended as it may cause your database to look messy.
3.
Merge-‐key Method: Cross-‐reference the two relations to create a third relation that will contain the foreign keys of the two relations. These foreign keys will serve as its primary keys. This method is recommended if both relations have low participation.
There are 2 ways to convert binary 1:N or N:1 relationships: 1.
Include the primary key of the “1-‐sided” relation as a foreign key in the “N-‐sided” relation.
Jason Thai Alexzander Avila 24 CMPS 342 2. If the participation is low on the “N-‐sided” relation, they can cross-‐reference to create a third relation as described above. Binary M:N must be cross-‐referenced as described above. There are 4 ways to convert superclass and subclass relationships: 1.
Create a relation for each super and subclasses, and include the primary key of the super class as a foreign key in its subclasses. This is the safest method but may require joins to give the appropriate results of the desired attribute sets.
2.
Don’t make a relation for the superclass but make a relation for each subclasses. Instead, include the corresponding superclass’s attributes into the relations so that they have a full attribute set for each specialization. This method should only be used if the participation of the subclasses add up to the total participation. That is, they shouldn’t be overlapping participation or else there would be duplicate entries.
3.
Merging the superclass and subclasses to create a single relation with only one single type attribute. This may result in some nulls depending on how many attributes the subclasses have. This method requires the subclasses to be disjointed. This will allow the relation to designate the subclass by the type attribute.
4.
Similar to creating a single relation with a single type attribute, but instead a single relation with multiple type attributes. In this method, each type will have a separate (Boolean) type attribute. Here, subclasses may overlap.
In any cases, multi-‐valued attributes should be stored in a new relation. It will contain the foreign key of the primary key of the entity/relationship where the attribute was
Jason Thai Alexzander Avila 25 CMPS 342 originated. There will be a single tuple for every single value. If there are any composite attributes, they will be split into simple attributes. In recursive relationships, just implement a new foreign key in the relation it’s referencing from. Ternary and N-‐ary relationships must have a new relation to contain the foreign keys of the primary keys of the relations associated with it. There are 2 ways to convert categories or union types: 1. Create a new relation that will contain any category attributes and in addition, the surrogate key. This key will serve as the primary key and will be implemented as the foreign key in each of the superclasses. 2. However, a surrogate key isn’t needed if the superclasses all share the same primary key. In this case, just use the common primary key.
2.2.3 Database Constraints
Constraints are like rules or restrictions in database. They help make databases
more accurate and precise by avoiding any mistakes in managing information.
The entity constraint ensures that each entity has a primary key. The primary key
constraint makes sure that the primary key in the relation is unique and not null, while the unique key constraint makes sure that every attribute is unique for every tuple within the relation. Referential constraints ensure that foreign keys can either be null or reference a primary key from its parent’s relation. Check constraints check each row to see if it meets a certain requirement before updating the table. Business rules are just specific rules given by the business itself.
Jason Thai Alexzander Avila 26 CMPS 342 All the above, except check constraints and business rules, are enforced by the DBMS (database management system) through settings in the database schema.
2.3 Convert Your E-‐R Database into a Relational Database
The purpose of this section is to clearly define and outline each relation in the
database and to develop sample queries using those relations.
Jason Thai Alexzander Avila CMPS 342
27
2.3.1 Relational Schema For The Database
Employee Relation Attributes: Attribute:
Domain/Type:
Employee Name
String; max 45 chars; not-‐nullable
Employee ID
Integer; 4 digit number; not-‐nullable
Address
String; max 50 chars; not-‐nullable
Salary
Float; not-‐nullable
Gender
Char; 1 char; not-‐nullable
Date of Birth
Char Array; 3 values; not-‐nullable
Ssn
String; 9 digits; not-‐nullable
Start/End Date
String; not-‐nullable
Constraints:
Primary Key: The Employee ID is the Primary Key as it is unique to each
individual employee and is used throughout the database to track and alter the Employee file.
Foreign Key: The Employee ID is a foreign key from their respective relations.
Rule: The SSN Relation must contain only numbers, although it is of a string
Data Type; if the End Date is not inputted by default, the employee is still employed. The default value for Salary would be the assumed minimum wage (currently $9/hour). Candidate Key:
The Candidate Key in the Employee relation is the Employee ID.
Jason Thai Alexzander Avila CMPS 342
28
Item Relation Attributes: Attribute:
Domain/Type:
Price
Float; not-‐nullable
Product ID
Integer; not-‐nullable; 4-‐8 digit number
Ingredients
Char array; not-‐nullable
Allergens
Char array; nullable
Nutritional Info
String; not-‐nullable
Constraints:
Primary Key: The Primary Key in this Attribute Set is the Product ID as it
defines each individual Item as well as sorts each item by their numeric code and helps the Company with ordering.
Foreign Key: The Foreign Key is the Product ID because it links the Item and
Order Relation as well as the Item and Supplier Relation.
Rule: The Price is the price per unit ordered. The Allergens is nullable
because not all items contain ingredients that an individual is possibly allergic to; the default value is NULL. The Nutritional Information must be accurate and informative of all nutrition values (i.e. Sodium, calories, fat, protein, etc.). Candidate Key:
The Candidate Key in this Attribute Set is the Product ID.
Jason Thai Alexzander Avila CMPS 342
29
Supplier Relation Attributes: Attribute:
Domain/Type:
Prices:
Float; not-‐nullable
Delivery Date/Time
String; not-‐nullable
Invoice ID
Integer; not-‐nullable; 6-‐8 digit number
Product ID
Integer; not-‐nullable; 4-‐8 digit number
Constraints:
Primary Key: The Primary Key in this Attribute Set is the Invoice ID because it
is unique for each order and provides both parties with the identification of the order.
Foreign key: The Foreign Key is the Product ID because it links the Supplier
Relation to the Item Relation. Candidate Key:
The Candidate Key in this Attribute Set is the Invoice ID.
Jason Thai Alexzander Avila CMPS 342
30
Customer Relation Attributes: Attributes:
Domain/Type:
Customer Name
String; nullable
Customer ID
Integer; not-‐nullable; 3 digit number
Order ID
Integer not-‐nullable; 3 digit number
Allergies
String; nullable
Constraints:
Primary Key: The Primary Key in the Attribute Set is the Customer ID because
it is unique to each individual customer.
Foreign Key: The Foreign Key in the Attribute Set is the Order ID because it
links the Customer Relation and the Order Relation.
Rule: The Customer Name can be made nullable because not all customers are
willing to give their name, and the case that the customer is a “regular” customer and well known to the employees. The Order ID must be synonymous to the Order that belongs to the Customer. The default value for Allergies is “NULL”. Candidate key:
The Candidate Key in this Attribute Set is the Customer ID.
Jason Thai Alexzander Avila CMPS 342
31
Survey Relation Attributes: Attributes:
Domain/Type:
Date/Time
String; not-‐nullable
Employee Performance
String; nullable
Customer Satisfaction
String; not-‐nullable
Survey ID
Integer; not-‐nullable; 4 digit number
Employee ID
Integer; not-‐nullable; 4 digit number
Customer ID
Integer; not-‐nullable; 3 digit number
Constraints:
Primary Key: The Primary Key in this Attribute Set is the Survey ID because it
is unique to each Survey and sorts each Survey numerically.
Foreign Key: The Foreign Keys are the Employee ID and Customer ID because
they link together the Survey Relation with the Employee and Customer.
Rule: Employee Performance is nullable because it is optional to fill out in the
Survey; default value is “NULL”. The Customer Satisfaction is not nullable because it is a required field in the Survey and must be filled with some content before it can be submitted. Candidate Key:
The Candidate Key in this Attribute Set is the Survey ID.
Jason Thai Alexzander Avila CMPS 342
32
2.3.2 Sample Data of Relation
Employee: E.name
E.ID
Addr
Salary
Sex
Bday
SSN
S/EDate
John Doe 1234
123 $9/hour M Happy St
4/25/93 123-‐45-‐ 6789
6/13/10 -‐current
Adam Smith
5678
1345 Main St
8/12/ 97 456-‐52-‐ 9458
4/12/11 -‐ 4/13/12
Jenny Alvarez
4790
1935 $9.25 Giant Dr /hour
F
10/3/90 492-‐05-‐ 1984
1/24/09 -‐ current
Maria Perez
4920
48204 Flower Way
$9/hour F
12/5/94 492-‐49-‐ 5864
2/4/06 -‐ 3/6/09
Jason Chow
6743
3858 $9/hour M House St
8/19/88 299-‐70-‐ 5869
4/8/14 -‐ 10/4/14
Chris Alvarez
1135
576 Teacup Way
$10 /hour
M
7/25/85 664-‐38-‐ 3948
6/16/09 -‐ current
Alex Cruz 9503
6583 Dream Dr
$9/hour M
10/13/9 490-‐43-‐ 3 4422
3/16/10 -‐ current
Abby Gail
4829
386 Ace $9.25 Ct /hour
F
2/29/90 486-‐28-‐ 5830
2/14/08 -‐ 3/16/14
Briana Garcia
5765
4952 Wilson Rd
$9/hour F
10/30/9 574-‐68-‐ 5 5729
5/16/04 -‐ current
$9/hour M
Jason Thai Alexzander Avila CMPS 342
33
Order: Total Cost
Order ID
Item List
Date/Tim EmpID e
Product ID
$3.01
#089
2 cheeseburgers
4-‐14-‐15/ 4859 5:45pm
2x53567
$2.32
#045
Lrg Fry
9-‐04-‐15/ 5738 1:14pm
57759
$7.69
#094
Combo #3
3-‐06-‐15/ 2899 6:52pm
26496 47498 12347
$1.07
#004
Sml Soda
6-‐19-‐15/ 2654 11:23am
74392
$15.43
#100
2 cheeseburgers 7-‐13-‐15/ 1290 Combo #4 7:33pm Lrg Fry
2x53567 48959 57759
$4.65
#076
Med Fry Lrg Sundae
3-‐15-‐15/ 5738 3:05pm
57738 69994
$10.86
#087
4 cheeseburgers 2 Lrg Fries Sml Soda
4-‐26-‐15/ 9684 10:19pm
4x53567 2x57759 74392
$3.39
#024
Lrg Fry Sml Soda
2-‐03-‐15/ 9658 9:00am
57759 74392
$8.62
#001
Combo #1
1-‐05-‐15/ 9702 4:56pm
75492
$5.15
#077
2 cheeseburgers 2 Sml Sodas
12-‐08-‐15/ 7592 10:21am
2x53567 2x74392
Jason Thai Alexzander Avila CMPS 342
34
Item: Name
Price
Product ID
Ingredients
Allergens
Nutritional Information
Cheeseburger $1.50
53567
Bread, Meat, Wheat, Soy, Sodium-‐200mg Pickles, Egg, Cal-‐300 Ketchup, Tomato Protein-‐14g Mustard
Sml Soda
$1.07
54903
Carbonated Water Corn Syrup
NULL
Sodium-‐60mg Cal-‐250 Protein-‐0g
Lrg Fry
$2.32
47204
Potatoes
Soy
Sodium-‐450mg Cal-‐375 Protein-‐6g
Lrg Sundae
$3.21
57204
Milk
Dairy
Sodium-‐40mg Cal-‐340 Protein-‐3g
Salad
$5.45
42950
Lettuce, Onions, Almonds
Nuts
Sodium-‐250mg Cal-‐450 Protein-‐5g
Bacon-‐ $2.25 Cheeseburger
48205
Bacon, Bread, Wheat, Soy, Sodium-‐340mg Meat, Pickles, Egg, Cal-‐450 Ketchup, Tomato Protein-‐18g Mustard
Apple Pie
$2.45
58295
Apple, Bread Wheat, Apples
Double-‐ $2.15 Cheeseburger
28304
Bread, Meat, Wheat, Soy, Sodium-‐350mg Pickles, Egg, Cal-‐450 Ketchup, Tomato Protein-‐20g Mustard
Corn Dog
$1.50
46238
Corn, Meat
Corn, Egg
Sodium-‐220mg Cal-‐125 Protein-‐6g
Jalapeno Poppers (3 pieces)
$1.59
27495
Jalapeno, Cheese
Dairy
Sodium-‐300mg Cal-‐220 Protein-‐3g
Sodium-‐120mg Cal-‐300 Protein-‐3g
Jason Thai Alexzander Avila CMPS 342
35
Supplier: Prices
Delivery Date/Time
Invoice ID
Product ID
$74.65
4-‐14-‐15/4:35pm
7483969
74939
$114.76
2-‐12-‐15/5:13pm
1991848
99794
$85.20
10-‐16-‐15/10:48am
7496730
62646
$58.69
11-‐25-‐15/1:48pm
1153859
27482
$148.24
9-‐13-‐15/11:56am
4729583
26648
$99.82
6-‐05-‐15/8:43am
1434487
96039
$86.59
5-‐16-‐15/5:13pm
4672955
85803
$79.22
4-‐19-‐15/1:46pm
5830658
27264
$127.83
9-‐28-‐15/3:63pm
6367389
64673
$104.41
11-‐21-‐15/2:31pm
4850628
89582
Customer: Customer Name Order ID
Customer ID
Allergies
Sally Sue
#046
#024
NULL
Devon Montoya #094
#086
Nuts
John Smith
#100
#041
NULL
Alice Moore
#068
#068
NULL
Chad Guttierez
#085
#032
Dairy
Lorenzo Padres #067
#085
Soy
NULL
#074
#100
NULL
NULL
#004
#031
NULL
Alicia Johnson
#095
#006
Dairy
Jason Thai Alexzander Avila CMPS 342
36
Survey: Date/Time
Employee Customer Survey ID Performance Satisfaction
Employee ID Customer ID
4-‐18-‐15/ 4:43pm
NULL
9
7492
9979
#048
6-‐13-‐15/ 11:23am
“John was helpful”
10
4748
4729
#087
8-‐05-‐15/ 5:34pm
“Emily was 3 on her phone the entire time”
6920
5839
#014
5-‐23-‐15/ 2:56pm
NULL
7
2749
2628
#058
11-‐14-‐15/ 8:45pm
“Jason was very nice”
8
2264
5960
#006
12-‐29-‐15/ 5:23pm
“Emilia was 9 very polite”
4772
7498
#076
4-‐03-‐15/ 3:51pm
NULL
5
5729
2582
#094
5-‐28-‐15/ 2:13pm
NULL
6
5629
6869
#086
3-‐15-‐15/ 12:46pm
“Disappointe 1 d”
1423
9967
#030
2-‐09-‐15/ 11:25am
NULL
4652
6970
#026
4
Jason Thai Alexzander Avila CMPS 342
37
List of Relations: Employee(E.Name, E.ID, Address, Salary, Gender, Bday, SSN, Start Date, End Date) Order(Total Cost, O.ID, Item List, Date/Time, E.ID, P.ID) Item(Name, Price, P.ID, Ingredients, Allergens, Nutritional Info) Supplier(Prices, Delivery Date/Time, I.ID, P.ID) Customer(C.Name, C.ID, O.ID, Allergies) Survey(Date/Time, Employee Performance, Customer Satisfaction, S.ID, E.ID, C.ID)
Jason Thai Alexzander Avila CMPS 342
38
2.4 Sample Queries to Our Database
In order to have a better understanding of our database, it’s best to know and
understand how we can use queries to access info within our database.
2.4.1 Design of Queries 1. List the Employees who have a birthday in March. 2. List the Customers who rated their Satisfaction 7 or higher. 3. List the largest order filled by every Employee on 07-‐08-‐15. 4. List the Items that were delivered on 04-‐23-‐15. 5. List the Employees who make/made more than minimum wage and have/had
worked more than 3 years from 2010 (assume $9/hour). 6. List the Customers who filled out a survey. 7. List the Customers who have an allergy to the food offered 8. List the Items that have more than 5g of protein 9. List the Employees that worked during the 07-‐25-‐15 robbery. 10. List the Employees who live together on 1865 Flower Dr.
Brief Description of Each Relational Expressions for Queries
Relational algebra is a procedural expression that composes of queries using
operators to accept one or two relations to return one relation.
Jason Thai Alexzander Avila 39 CMPS 342 Tuple relational calculus is a declarative expression that uses tuple variables and formulas to compute the result of any tuples that make the formula true.
Domain relational calculus is similar to TRC, but instead uses domain values to
compute the result of any tuples that make the formula true.
2.4.2 Relational Expressions for Queries: 1. List the Employees who have a birthday in March.
Relational Algebra: π!.!"#$ σ!.!"#$!""#$%&" employee Tuple Relational Calculus: {e| Employee(e) ^ e.BDay[2] = “March”} Domain Relational Calculus: {e| Employee(e,_,_,_,_,[2] = “March”,_,_,_)} 2. List the Customers who rated their Satisfaction 7 or higher.
Relational Algebra: π!.!"#$ (σ!.!"#!!.!"# ^ !.!"#$!%"&#$'(!! customer ∗ survey )
Tuple Relational Calculus: {c| Customer(c) ^ (Ǝs)(Survey(s) ^ c.CID = s.CID ^ s.satisfaction >= 7) } Domain Relational Calculus: {| Customer(c,cd,_,_) ^ Survey(_,_,>=7,_,_,cd)}
Jason Thai Alexzander Avila CMPS 342 3. List the largest order filled by every Employee on 07-‐08-‐15.
40
Relational Algebra: order – πo.*(σo1.totalcost < o2.totalcost(order x order)) Tuple Relational Calculus: {e| Employee(e) ^ (Ǝo1)(Ǝo2)(Order(o1) ^ Order(o2) ^ o1.totalcost > o2.totalcost ^ o2.date/time = “07-‐08-‐15”)} Domain Relational Calculus: {| Employee(e,ed,_,_,_,_,_,_,_) ^ (Ǝtc1)(Ǝtc2)(Order(tc1,_,_,_,ed,_) ^ Order(tc2,_,_,_,ed,_) ^ (tc1>tc2))} 4. List the Items that were delivered on 04-‐23-‐15.
Relational Algebra: πi.name(σs.deliverydate = “04-‐23-‐15”(item * supplier)) Tuple Relational Calculus: {i| Items(i) ^ (Ǝs)(Supplier(s) ^ s.Deliverydate = “04-‐23-‐15” ^ I.PID = s.PID)} Domain Relational Calculus: {| Item(i,_,pd,_,_,_) ^ (Ǝd)(Supplier(_,d,_,pd) ^ d = “04-‐23-‐15”)} 5. List the Employees who make/made more than minimum wage and
have/had worked more than 3 years from 2010. Relational Algebra:
Jason Thai Alexzander Avila CMPS 342
41
σe.salary > 9.00 ^ e.sDate >= “01-‐01-‐2010” ^ e.eDate >= “01-‐01-‐2013”(employee) Tuple Relational Calculus: {e| Employee(e) ^ e.Salary > 9.00 ^ e.sDate >= “01-‐01-‐2010” ^ (e.eDate >= “01-‐01-‐ 2013” v e.eDate = “current”)} Domain Relational Calculus: {e| Employee(e,_,_,>9.00,_,_,_,>=”01-‐01-‐10”,(>=”01-‐01-‐2013” v “current”)} 6. List the Customers who filled out a Survey on 01-‐26-‐14
Relational Algebra: πc.name(σc.cid = s.cid ^ s.date = “01-‐26-‐14”(customer * survey)) Tuple Relational Calculus: {c| Customer(c) ^ (Ǝs)(Survey(s) ^ c.CID = s.CID ^ s.Date = “01-‐26-‐14”)} Domain Relational Calculus: {| Customer(c,cd,_,_) ^ (Survey(“01-‐26-‐14”,_,_,_,_,cd)} 7. List the Customers who have an allergy to the food offered.
Relational Algebra: πc.name(σc.allergies = i.allergies(customer * item)) Tuple Relational Calculus: {c| Customer(c) ^ (Ǝo)(Order(o) ^ (Ǝi)(Item(i) ^ c.Allergies = i.Allergies ^ c.OID = o.OID ^ o.PID = I.PID)}
Jason Thai Alexzander Avila CMPS 342
42
Domain Relational Calculus: {| (Ǝo)(Customer(c,_,o,a) ^ Order(_,o,_,_,_,pd) ^ Item(n,_,pd,_,a,_,_))} 8. List all Orders that contain a drink.
Relational Algebra: σo.itemlist = “sml soda” v o.itemlist = “med soda” v o.itemlist = “lrg soda”(order) Tuple Relational Calculus: {o| Order(o) ^ (o.ItemList = “Sml Soda” v o.ItemList = “Med Soda” v o.ItemList = “Lrg Soda”} Domain Relational Calculus: {o| Order(_,o,(“Sml Soda” v “Med Soda” v “Lrg Soda”,_,_, _) 9. List the Employees that worked between the dates of 07-‐15-‐15 and 07-‐
31-‐15. Relational Algebra: σe.sdate = “7-‐15-‐15”(employee) Tuple Relational Calculus: {e| Employee(e) ^ e.sDate = “7-‐15-‐15”} Domain Relational Calculus: {e| Employee(e,_,_,_,_,_,_,=”7-‐15-‐2015”)}
Jason Thai Alexzander Avila CMPS 342 10. List the Employees who live together on 1865 Flower Dr.
43
Relational Algebra: πe1.name, e2.name(σe1.eid != e2.eid ^ e1.address = “1865 Flower Dr.” ^ e2.address = “1865 Flower Dr.”(employee x employee)) Tuple Relational Calculus: {e| Employee(e) ^ e.address = “1865 Flower Dr” ^ (Ǝe2)(Employee(e2) ^ e2.address = e.address)} Domain Relational Calculus: {| Employee(e1,_,”1865 Flower Dr”,_,_,_,_,_,_) ^ Employee(e2,_,”1865 Flower Dr”,_,_,_,_,_,_)}