微信客服：xiaoxionga100
微信客服：ITCS521
COMM2822-数据库代写
时间：2023-03-09
LiC: Vincent Pang
vincent.pang@unsw.edu.au
Lecturer: George Joukhadar
g.joukhadar@unsw.edu.au
Tutor: Liam Li Chen
liamli.chen@student.unsw.edu.au
Intro to Databases for Business Analytics
Week 3: Relational Modelling
Copyright Notice
• There are some file-sharing websites that specialise in buying and selling academic work to
and from university students.
• If you upload your original work to these websites, and if another student downloads
and presents it as their own either wholly or partially, you might be found guilty of collusion
— even years after graduation.
• These file-sharing websites may also accept purchase of course materials, such as copies
of lecture slides and tutorial handouts. By law, the copyright on course materials,
developed by UNSW staff in the course of their employment, belongs to UNSW. It
constitutes copyright infringement, if not academic misconduct, to trade these
materials.
Copyright
UNSW Business School. (2021, July 24). Acknowledgement of Country [online video].
Retrieved from https://vimeo.com/369229957/d995d8087f
UNSW Business School acknowledges the Bidjigal
(Kensington campus) and Gadigal (City campus)
the traditional custodians of the lands where each
campus is located.
We acknowledge all Aboriginal and Torres Strait
Islander Elders, past and present and their
communities who have shared and practiced their
teachings over thousands of years including
business practices.
We recognise Aboriginal and Torres Strait Islander
people’s ongoing leadership and contributions,
including to business, education and industry.
Acknowledgement of Country
Country
Chapter 3
The Relational Database Model
3-1 to 3-7 - pp. 69-99
Agenda
❑ Smarthinking
❑ Housekeeping
❑ Kahoot activity
❑ Relational Modelling:
▪ ER Model Recap
▪ Relational Model Recap
▪ Week 2 Exercise – Relational Model
▪ Korean BBQ Restaurant – Relational Model
❑ SQL
▪ SQL Basics + Table creation
▪ Korean BBQ Restaurant – Build Script
❑ Assignment Q/A
Smarthinking
Agenda
❑ Smarthinking
❑ Housekeeping
❑ Kahoot activity
❑ Relational Modelling:
▪ ER Model Recap
▪ Relational Model Recap
▪ Week 2 Exercise – Relational Model
▪ Korean BBQ Restaurant – Relational Model
❑ SQL
▪ SQL Basics + Table creation
▪ Korean BBQ Restaurant – Build Script
❑ Assignment Q/A
Student Ambassador Program
❑ What is the Student Ambassador Program?
A student-as-partner initiative drives partnership between academics and students in co-creating a
successful learning experience, giving students the ‘control’ and opportunity to make sense of
learning through a collaborative effort with the teaching team of the course. Student
Ambassadors will engage with course academics throughout the term to brainstorm, design, and
develop greater understanding of the learning processes involved.
❑ Expectation:
▪ NO defined expectation
▪ Proposed fortnightly meetings (TBC) in weeks 3, 5, 7 and 9
▪ Scope of discussion: lectures, tutorials, assessments, course structure, and many more…
▪ Any topic that is of interest to you or the course
▪ If interested, Express your interest via this Teams channel.
• https://teams.microsoft.com/l/channel/19%3aab0cb64ad4e348f68a066db69c5633a0%40thread.tacv
2/Student%2520Ambassadors?groupId=ea0ceb54-4777-4d44-a863-
f83fdab88d64&tenantId=3ff6cfa4-e715-48db-b8e1-0867b9f9fba3m
COMM2822 Consultation
What Where When How
Consultation with Vincent Quad 2088 Wednesday 14:00 – 15:00 Please come to my office.
Consultation with George Online Tuesday 9.30 – 10.30 Send an email to book a time:
g.joukhadar@unsw.edu.au
Consultation with Liam Online Wednesday 11:00 – 11:30 Send an email to book a time:
liamli.chen@student.unsw.edu.au
PASS Class (for both INFS1603 and COMM2822)
Day/Time Name Location
Tuesday 2:00 –3:00pm Christopher Choo UNSW Business School Room 220
11
Week 2 to Week 10 Including Week 6
Agenda
❑ Smarthinking
❑ Housekeeping
❑ Kahoot activity
❑ Relational Modelling:
▪ ER Model Recap
▪ Relational Model Recap
▪ Week 2 Exercise – Relational Model
▪ Korean BBQ Restaurant – Relational Model
❑ SQL
▪ SQL Basics + Table creation
▪ Korean BBQ Restaurant – Build Script
❑ Assignment Q/A
Kahoot
Agenda
❑ Smarthinking
❑ Housekeeping
❑ Kahoot activity
❑ Relational Modelling:
▪ ER Model Recap
▪ Relational Model Recap
▪ Week 2 Exercise – Relational Model
▪ Korean BBQ Restaurant – Relational Model
❑ SQL
▪ SQL Basics + Table creation
▪ Korean BBQ Restaurant – Build Script
❑ Assignment Q/A
Desirable Primary Key Characteristics
Unique value Cannot be null
Non intelligent
Preferably single-attribute
Preferably numeric
Security-compliant
15
Should not have embedded semantic meaning, e.g., use zID as PK
rather than name
No change over time Name, marital status may change
Remember a PK can be someone’s foreign key, multiple
attributes make it hard to link tables
To avoid typing errors; Can use auto-increment, e.g., zID
Using Social Security Number (SSN) as a SID is a bad idea.
ER Recap
▪ Composite Entity
Agenda
❑ Smarthinking
❑ Housekeeping
❑ Kahoot activity
❑ Relational Modelling:
▪ ER Model Recap
▪ Relational Model Recap
▪ Week 2 Exercise – Relational Model
▪ Korean BBQ Restaurant – Relational Model
❑ SQL
▪ SQL Basics + Table creation
▪ Korean BBQ Restaurant – Build Script
❑ Assignment Q/A
Database Design: Overview
When designing database for an organization, the processes that we follow are:
❑ Gather business requirements.
❑ Develop conceptual model using ER modelling technique (Weeks 1 and 2).
❑ Convert ER model to a set of relations in the relational model (Week 3).
❑ Normalize the relations to remove any anomalies (Weeks 4 and 5)
❑ Implement the database by creating a table for each normalized relations.
18
Relational Model
❑“A relational model represents data in a two dimensional table called a relation.”
❑ Relational model includes:
• Relations: two dimensional tables
• Attributes: the column headers of a relation.
• Tuples: the rows of a relation.
❑ The name of a relation (table) and its set of attributes (column headers) are called a
schema for the relation.
❑ The set of schemas for all relations in a design is called a database schema
(metadata).
❑ The data dictionary describes the database schema
❑ Usually implemented in a RDBMS (relational database management system) such as
Oracle.
19
Relational Model Example
❑Relational schema for the relation “MOVIE”:
• MOVIE (TITLE, YEAR, LENGTH)
❑Relation:
• Every relation has a unique name.
• Every attribute value is atomic.
• Every row is unique.
• Attributes in tables have unique names.
• The order of the columns is irrelevant.
• The order of the rows is irrelevant.
20
Relational Model Example
21
22
Relational Model
Relational schema:
PRODUCT (Prod_Code, Prod_Descript, Prod_Price, Prod_On_Hand, Vend_Code)
VENDOR (Vend_Code, Vend_Contact, Vend_AreaCode, Vend_Phone)
23
^^ Use this format for your assignment!
Summary of Most Important Rules
❑ ER entity types become relational schemata (relations).
❑ ER relationships become relational schemata OR references with FKs in the
schemata/tables.
❑ ER attributes of an entity type become attribute column headers in the schemata.
❑ Entity instances are the rows (tuples, relational instances) in the actual tables.
❑ Connectivity and cardinality are indirectly expressed through existence of schema,
references with FKs, number of rows going into any particular relation, settings such as
NOT NULL for the FK columns in the data dictionary, etc.
24
Agenda
❑ Smarthinking
❑ Housekeeping
❑ Kahoot activity
❑ Relational Modelling:
▪ ER Model Recap
▪ Relational Model Recap
▪ Week 2 Exercise – Relational Model
▪ Korean BBQ Restaurant – Relational Model
❑ SQL
▪ SQL Basics + Table creation
▪ Korean BBQ Restaurant – Build Script
❑ Assignment Q/A
Ex 2
Draw an ER diagram for this situation (state any assumptions that you make). Based on the
ER diagram, draw the relevant relational model.
• The firm has a number of sales offices in several states. Attributes of sales office include
Office_number (identifier) and Location
• Each sales office is assigned one or more employees. Attributes of employee include
Employee_ID (identifier) and Employee_Name. An employee must be assigned one only
one sales office
• For each sales office, there is always one employee assigned to manage that office. An
employee may manage only the sales office to which he or she is assigned.
• The firm lists property for sale. Attributes of property include Property_ID (identifier) and
Location. Components of Location include Address, City, State, and Zip_Code
• Each unit of property must be listed with one (and only one) of the sales offices. A sales
office may have any number of properties listed or may have no properties listed.
• Each unit property has one or more owners. Attributes of owners are Owner_ID (identifier)
and Owner_Name. An owner may own one or more units of property. An attribute of the
relationship between property and owner is Percent_Owned
Agenda
❑ Smarthinking
❑ Housekeeping
❑ Kahoot activity
❑ Relational Modelling:
▪ ER Model Recap
▪ Relational Model Recap
▪ Week 2 Exercise – Relational Model
▪ Korean BBQ Restaurant – Relational Model
❑ SQL
▪ SQL Basics + Table creation
▪ Korean BBQ Restaurant – Build Script
❑ Assignment Q/A
Korean BBQ Restaurant
Supplier: A list of companies which supply raw ingredients to the Korean BBQ Restaurant.
This table contains a unique identification number for the supplier, the name of the
company, the category of ingredients supplied, and their phone number.
Produce: A list of produce which is used by the Korean BBQ Restaurant. These are the
items which were supplied by the suppliers. This table contains a unique identification
number for the raw ingredient, the identification number for its supplier, and the name of the
raw ingredient.
Drinks: A list of drinks which is sold at the Korean BBQ Restaurant. This table contains a
unique identification number for each drink, the identification number for its supplier, the
name of the drink, and the price of the drink.
Chef: A list of chefs who work at the Korean BBQ Restaurant. This table contains a unique
identification number for each chef, the name of the chef, their gender, salary, and phone
number.
Food: A list of food items which are sold at the Korean BBQ Restaurant. This table contains
a unique identification number for each dish, the name of the dish, the preparation time for
each dish in minutes, the price of each dish, and any associated allergies.
Korean BBQ Restaurant (con’t)
Cooks: A list of which raw ingredients are used to produce each dish, and the chef who cooks
the dish. This table contains the unique identification numbers for chefs, produce, and food.
Customer: A list of existing customers who have visited the Korean BBQ Restaurant at least
once. This table contains a unique identification number for each customer and their name.
Discounts: A list of discounts offered by the Korean BBQ Restaurant. This table contains a
unique identification number for each discount, the name of the discount, and the percentage of
the discount.
Delivery Service: A list of delivery companies who work in partnership with the Korean BBQ
Restaurant. This table contains a unique identification number for each delivery company, the
name of the delivery company, the type of vehicle used for delivery, and their respective service
fee.
Orders: A list of orders made at the Korean BBQ Restaurant, including the food and drink items
that were ordered, the customer who ordered them, and any relevant discount or delivery
service. This table contains the unique identification numbers for customers, food, drinks,
discounts, and delivery service, as well as the date on which the order was placed, and whether
the customer ate in store or at home.
Korean BBQ Restaurant (con’t)
❑ Create an ER diagram for the Korean BBQ restaurant (w2 tut)
❑ In groups of 2, using the ER diagram create in week 2 tutorial,
provide a relational model/relational database schema
Agenda
❑ Smarthinking
❑ Housekeeping
❑ Kahoot activity
❑ Relational Modelling:
▪ ER Model Recap
▪ Relational Model Recap
▪ Week 2 Exercise – Relational Model
▪ Korean BBQ Restaurant – Relational Model
❑ SQL
▪ SQL Basics + Table creation
▪ Korean BBQ Restaurant – Build Script
❑ Assignment Q/A
Chapter 7
Introduction to
Structured Query
Language (SQL)
SeQueL
38
Relational Languages
Relational DBMS’s query languages (e.g., SQL in Oracle) contain three components:
Data Definition Language (DDL): used to specify/modify the database schema.
Data Control Language (DCL): used to control the DB (e.g., user rights).
Data Manipulation Language (DML): used to retrieve/manipulate data.
39
SQL
❑ SQL = Structured Query Language = Sequel
❑ SQL is the first standard database language.
❑ Originally developed by D. Chamberlin and R. Boyce at IBM.
❑ The most common SQL standard is the ANSI/ISO SQL. Originally defined
in 1988, SQL-86, it has been undergone major revisions in 1992, SQL-92,
and 1999, SQL-99. The latest revision is SQL:2011.
❑ Microsoft, Oracle, and other vendors have introduced deviations from
ANSI SQL.
❑ As a relational language, SQL has three main components:
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
40
SQL DDL (Data Definition Language)
❑ To create the database structure:
CREATE SCHEMA AUTHORIZATION Creator
Example: CREATE SCHEMA AUTHORIZATION Chris
CREATE DATABASE Database_Name
Example: CREATE DATABASE Student
❑ To create tables:
CREATE TABLE Table_Name (column_name data_type [NULL|NOT NULL] [,…])
SQL DDL
❑ Example of table creation:
CREATE TABLE COURSE (
COURSE_CODE CHAR (8) NOT NULL,
COURSE_NAME VARCHAR (18) NOT NULL,
PRGRAM_CODE CHAR (4) NOT NULL,
SEMESTER CHAR (1),
PRIMARY KEY (COURSE_CODE),
FOREIGN KEY (PROGRAM_CODE)
);
(SQL example from DBMS Microsoft Access)
42
SQL DDL
❑ Example of table creation:
CREATE TABLE COURSE_1 (
COURSE_CODE VarCHAR (8),
COURSE_NAME VarCHAR (8),
CONSTRAINT COURSE_CODE_PK PRIMARY KEY
(COURSE_CODE)
);
43
SQL DML (Data Manipulation Language)
❑ ANSI/ISO SQL standard use the terms “tables,” “columns” and “rows”
(not relations, attributes, and tuples)
❑ The principal SQL DML statements are:
• SELECT
• INSERT
• UPDATE
• DELETE
❑ Complete SQL statements consists of reserved words and user-defined
words:
• The reserved words are fixed part of the language.
• The user-defined words represent the meaning of the data to the user (e.g., “users”,
“bookings”).
44
SQL Query Structures
❑ The SELECT statement is used to retrieve and display data from one or
more tables.
❑ Relation algebra’s selection, projection and join statements can be
performed with one single SELECT statement.
❑ “SELECT FROM WHERE”
• SELECT clause tells which attributes [columns] of the tuples [rows] matching the condition are
produced as part of the answer.
• FROM clause gives the names of relation(s) [table(s)].
• WHERE clause is a condition that tuples [rows] must satisfy in order to match the query.
45
SQL Query Structures
SELECT [DISTINCT | ALL] {| [column_expression AS new_name] [, …]}
FROM table_name [alias] [, …]
[WHERE condition]
[GROUP BY column_list]
[HAVING condition]
[ORDER BY column_list];
[] : indicates optional elements.
{} : indicates that the element may or may not appear.
| : indicates “or.”
; : indicates the end of the statement.
46
SQL Query Structures
EMPLOYEE (Employee_ID, Employee_FName, Employee_LName, Employee_HireDate, Employee_Title)
CERTIFIED (Employee_ID, Skill_ID, Certified_Date)
SKILL (Skill_ID, Skill_Name, Skill_Description)
47
SQL Query Structures
SQL allows us to use the keyword ALL to specify that all tuples are to be selected.
SELECT ALL
FROM EMPLOYEE;
or
SELECT *
FROM EMPLOYEE;
* : is a “wild card.”
48
SQL Query Structures
❑ The SQL syntax is basically:
SELECT
FROM ;
❑ List all Skill Name and Skill Description:
SELECT Skill_Name, Skill_Description
FROM SKILL;
❑ SQL supports the elimination of duplicates by using the keyword DISTINCT.
SELECT DISTINCT Employee_ID
FROM CERTIFIED;
49
WHERE Clause Options
❑ Selecting rows with conditional restrictions
• WHERE clause is used to add conditional restrictions to the SELECT statement that limit the
rows returned by the query
• Syntax:
SELECT columnlist
FROM tablelist
[WHERE conditionlist ]
[ORDER BY columnlist [ASC | DESC] ];
❑ Using comparison operators on character attributes
• May be used to place restrictions on character-based attributes
❑ Using comparison operators on dates
• Date procedures are often more software-specific than other SQL procedures
SQL Query Structures
For instance, in the previous example, we only interest in “Basic
Database Manipulation”, we can put a condition in the WHERE
clause:
SELECT Skill_Name, Skill_Description
FROM SKILL
WHERE Skill_Name = “Basic Database Manipulation”;
51
Mathematical Operators for SQL
Mathematical operators that can be used in a WHERE clause
for comparison:
= equal to
< less than
<= less than or equal to
> greater than
>= greater than or equal to
<> not equal to
Mathematical Operators for SQL
❑ Create a list of product description, product indate and product price for products sold
by vendor that are not coded “21344”.
SELECT P_Description, P_Indate, P_Price, V_Code
FROM PRODUCT
WHERE V_Code <> 21344;
❑ Create a list of product description, product onhand, product minimum, and product
price for products with product code less than “1558-QWI”.
SELECT P_Description, P_Onhand, P_Min, P_Price
FROM PRODUCT
WHERE P_Code < ‘1558-QWI’ ;
53
ASCII Codes in SQL
❑ All characters/signs are assigned an ASCII
(American Standard Code for Information
Interchange) code by the computer.
❑ Examples are below; see manual or online for
more information on ASCII codes.
❑ The comparisons of strings are made from
left to right. This is useful when comparing
names. However, it also may create problems:
▪ “2” is sorted as if greater than “11” (because “2” > “1”).
▪ “01/01/2020” is sorted before “12/31/2015” (because “0” <
“1”).
▪ Recommendation: use the date/number format instead of
string.
Logical (Boolean) Operators in SQL
Boolean Operators:
❑ OR
❑ AND
❑ NOT
❑ List products where the vendor code is ‘21344’ or ‘24288’:
SELECT P_Description, P_Indate, P_Price, V_Code
FROM PRODUCT
WHERE V_Code = 21344 OR V_Code = 24288;
❑ List products where either the product indate is after July 15, 2015 and the product price is less than 50.00 – or the vendor code is
24288.
SELECT P_Description, P_Indate, P_Price, V_Code
FROM PRODUCT
WHERE (P_Price < 50 AND P_Indate > ‘07/15/15’) OR V_Code = 24288;
55
Special Operators in SQL
❑ BETWEEN is used to define range limits.
❑ IS NULL is used to check whether an attribute value is null.
❑ LIKE is used to check for similar character strings.
❑ IN is used to check whether an attribute value matches a
value contains within a subset of listed values.
❑ EXISTS is used to check whether an attribute has a value.
56
Special Operators in SQL
❑ BETWEEN is used to define range limits.
Examples:
❑ List the products with prices between 50 and 100.
SELECT *
FROM PRODUCT
WHERE P_Price BETWEEN 50.00 AND 100.00;
/or/
SELECT *
FROM PRODUCT
WHERE P_Price >= 50.00 AND P_Price <= 100.00;
57
Special Operators in SQL
❑ LIKE is used to check for similar character strings.
❑ List the details of all vendors whose last name begins with “Smith.”
SELECT V_Name, V_Contact, V_AreaCode, V_Phone
FROM VENDOR
WHERE V_Contact LIKE ‘Smith%’;
% : wild card
58
Special Operators in SQL
❑ IN is used to check whether an attribute value matches a value contains within a subset of listed
values.
❑ List the contents of the product table where the product price is $ 50 or $ 100.
SELECT*
FROM PRODUCT
WHERE P_Price = 50.00 OR P_Price = 100.00;
or
SELECT*
FROM PRODUCT
WHERE P_Price IN (50.00, 100.00);
59
Special Operators in SQL
❑ IS NULL is used to check whether an attribute value is null.
❑ EXISTS is used to check whether an attribute has a value.
❑ List the details of products with existing (not-NULL) vendor codes.
SELECT *
FROM PRODUCT
WHERE V_Code EXISTS;
or
SELECT *
FROM PRODUCT
WHERE NOT ISNULL (V_Code);
60
Ordering SQL Results
❑ ORDER BY
❑ ORDER BY
order
❑ List the details of product table listed by product price in ascending
order:
SELECT P_Description, P_Indate, P_Price, V_Code
FROM PRODUCT
ORDER BY P_Price;
61
Ordering SQL Results
❑ List the details of of products with an in-date before 15
September 1999 and a price less than A$ 50.
❑ Put the results in ascending order of vendor code and
descending order of price.
SELECT P_Description, P_Indate, P_Price, V_Code
FROM PRODUCT
WHERE P_Indate < ‘9/15/99’ AND P_Price <= 50.00
ORDER BY V_Code, P_Price DESC;
62
Questions
63
Agenda
❑ Smarthinking
❑ Housekeeping
❑ Kahoot activity
❑ Relational Modelling:
▪ ER Model Recap
▪ Relational Model Recap
▪ Week 2 Exercise – Relational Model
▪ Korean BBQ Restaurant – Relational Model
❑ SQL
▪ SQL Basics + Table creation
▪ Korean BBQ Restaurant – Build Script
❑ Assignment Q/A
Korean BBQ Restaurant – Build Script
Download from Moodle
Agenda
❑ Smarthinking
❑ Housekeeping
❑ Kahoot activity
❑ Relational Modelling:
▪ ER Model Recap
▪ Relational Model Recap
▪ Week 2 Exercise – Relational Model
▪ Korean BBQ Restaurant – Relational Model
❑ SQL
▪ SQL Basics + Table creation
▪ Korean BBQ Restaurant – Build Script
❑ Assignment Q/A
