Introduction to Databases for Business Analytics
Week 10 Course Review
Term 2 2022
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:
Copyright By PowCoder代写 加微信 powcoder
PASS Leader:
• 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.
Acknowledgement of Country
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 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 Islander people’s ongoing leadership and contributions, including to business, education and industry.
UNSW Business School. (2022, May 7). Acknowledgement of Country [online video]. Retrieved from https://vimeo.com/369229957/d995d8087f
❑ Housekeeping
❑ MyExperience
❑ Exam Advice
❑ Course Review
❑ Thank you and Q&A
Within-Group Peer Review ❑ Email the LiC (form on Moodle)
Moodle to complete
or accessing myexperience.unsw.edu.au directly.
myExperience
Exam Advice
On different slides
Course Review
Topics and Key Concepts
❑ Business Rules ❑ Conceptual Model ❑ Relation Model
❑ Normalization
❑ RDBMS/SQL
❑ Big Data
Database Design: Overview
The processes that we follow when designing a database for an organization:
1. We gather business requirements from the organization.
2. We develop a conceptual model using ER modelling technique.
3. We convert the conceptual model (ER model) to a relational model, a set of
relations.
4. We normalize the relation model (relations) to remove any anomalies and
convert to internal model. (e.g., Oracle)
5. We physically implement this internal model in a database by creating a table
for each normalized relations.
Conceptual Model
What is a conceptual model?
❑ A conceptual data model is a representation of organizational data.
❑ ER modelling is common modelling technique (use Chen’s notation for exam).
❑ The result of ERM is an ER model: a detailed, logical representation of the data for an
organization or for a business area.
What is shown in an ER model?
❑ An ER model is normally expressed as an ER diagram, which is a graphical representation of an
❑ The ER model is expressed in terms of entities in the business environment.
❑ The ER model also shows the relationships (associations) among those entities.
❑ The ER model also shows attributes of both the entities and their relationships.
Note: In the exam, solve all ER questions without supertype/subtype structures (unless explicitly asked for)!
Relational Model
What is a relation a model?
❑ A relational model represents data in relations.
❑ A relation can be thought of (and implemented as) a two-dimensional table.
❑ The name of a relation and the set of attributes for a relation is called the relational
What is shown in a relation model?
❑ A relational model shows relations, the name and structure of a two-dimensional table. ❑ A relation model shows attributes, the names of the columns of relations.
❑ A relation model shows tuples, the rows of relations.
Normalization
❑ Normalization is a process for converting complex data structures into simple data structures. This can be accomplished in stages.
❑ What is the outcome of normalization?
❑ First normal form (1NF): Any repeating data have been removed, so there is a single value at the intersection of each row and column of the table.
❑ Second normal form (2NF): Non-key attributes require the whole key for identification. (No partial dependencies exist.)
❑ Third normal form (3NF): Non-key attributes do not depend on other non-key data elements (which is called transitive dependencies).
❑ Boyce- Form (BCNF): If no non-key attribute determines part of the PK.
❑ We usually normalize to 3NF, which is an industry standard.
Relational Languages – Theory What is the “theory” behind relational databases?
❑ Relational algebra and relational calculus are defined by Codd (1971) as the basis for relational languages. Both languages are not very user- friendly languages.
❑ Relational algebra operations can be classified in three main
categories:
❑ Union, Intersection & Difference: set operations.
❑ Selection & Projection: choose/remove parts of a relation.
❑ Cartesian Product & Join: operations combine the tuples of two relations.
Relational Languages – Practice What is the practice of relational databases?
❑ The RDBMS – for example Oracle – provides data access via a query language.
❑ The RDBMS’s query language – most commonly SQL – contains three
components:
❑ Data Definition Language (DDL) is used to specify the database schema or
modify an existing one.
❑ Data Manipulation Language (DML) is used to manipulate the data. ❑ Data Control Language (DCL) is used for controlling the data.
ISO SQL standard uses the terms tables, columns, and rows.
❑ SELECT clause tell which attributes of the tuples matching the condition are produces as part of the answer.
❑ FROM clause gives the names of relation(s).
❑ WHERE clause is a condition which tuples must satisfy in order to match
the query.
SLECT
Database Development
Database Development
❑ Information systems development overview ❑ Software development lifecycle (SDLC)
❑ Database development lifecycle (DBLC)
❑ Interaction between SDLC and DBLC
Database Administrator (DBA)
❑ DBA vs. Data Administrator (DA) ❑ DBA tasks
❑ DBA ethics
DBLC & SDLC
Database Administration (DBA)
When a new DBMS is introduced to an organization, three important
aspects have to be addressed.
❑ Technological ❑ Managerial
❑ Cultural
The person responsible for the control of the database is called the Database Administrator (DBA).
Database Administration (DBA) ❑ The role and position of DBA may vary in companies.
❑ Some of the larger corporations make a distinction between DA (data administrator) and DBA (database administrator).
❑ DA: A high-level function that is responsible for the overall management of data resources in an organization, including maintaining corporate-wide definitions and standards.
❑ DBA: A technical function that is responsible for physical database design and for dealing with technical issues such as security enforcement, database performance and backup and recovery.
What is Big Data?
❑ Buzz Word!
❑ Cannot fit into a USB flash drive ❑ A large and complex dataset
❑ Social media
❑ IoT streaming of data
❑ Capturing of Media
3Vs (Volume, Velocity and Variety) and more Vs
Big Data is classified into three types
❑ Structured
❑ Unstructured
❑ Semi-Structured
A view contains no data of its own.
❑ A view is a logical table based on a table or another view
A view is stored as a SELECT statement in the data dictionary.
❑ The tables on which a view is based are called base tables.
The two main purposes of using a view are to: ❑ Reduce the complexity of some queries; and
❑ Restrict users’ access to sensitive data
Exercise 1 – Aquariums
Each fish belongs to a species. Each fish also has a name, and you also want to register their colour and weight. You would like to store the specific food recommendation for each species. Naturally, the database should be able to tell you which fish is in which tank. Also, the names, volumes, and colours of the tanks should be stored. Finally, there are events involving your fish (birth, fights, etc.) to be stored with date and comment.
❑ Create the ER model.
❑ Create the relational model.
Solution to Exercise 1 – ER
Solution to Exercise 1 – Relational Model
Species (SpID, SPName, SpFood)
Fish (FishID, Fname, Color, Weight, SpID, TankID)
FishTank (TankID, Tname, Tcolor, Tvolume) Event (EventID, Edate, Comment, FishID)
Exercise 2 – SQL
Create SQLs for the following questions:
1. List all details about the fish stored in the fish table, sorted by fish name in
ascending order.
2. What is the average volume of a fish tank?
3. You have called your Clownfish “Sharky”. How many events involving Sharky
have occurred between 1 January 2012 and 1 April 2013? List the name of the
fish and the number of events!
4. How can you find about Sharky’s are of which type species? List the species
name and food.
5. What colours are the fish tanks? Do not include duplicates.
Solution to Exercise 2 – SQL
1. List all details about the fish stored in the fish table, sorted by fish name in ascending order.
SELECT * FROM FISH ORDER BY Fname;
2. What is the average volume of a fish tank?
SELECT AVG (Tvolume) “Average Volume” FROM FISH_TANK;
Solution to Exercise 2 – SQL
3. You have called your Clownfish “Sharky”. How many events involving Sharky have occurred between 1 January 2012 and 1 April 2013? List the name of the fish and the number of events!
SELECT FROM WHERE AND GROUP BY
Fname, COUNT (Fname) “Number of Events”
FISH JOIN EVENT USING (FishID)
Fname = ‘Sharky’
Edate BETWEEN DATE ‘2012-01-01’ AND ‘2013-04-01’ Fname;
Solution to Exercise 2 – SQL
4. How can you find about Sharky’s are of which type species? List the species name and food.
SELECT FROM WHERE
Fname, SPName, SpFood
FISH JOIN SPECIES USING(SpID) Fname = ‘Sharky’;
5. What colours are the fish tanks? Do not include duplicates.
SELECT DISTINCT (TColor) FROM FISHTANK
Exercise 3 – Normalization
Scenario: Using the INVOICE table structure below:
a. Normalize the table to 1NF, and draw the dependency diagram. Assume that the table does not contain
repeating groups and that an invoice-number references more than one product.
b. Normalize the table to its 2NF, and indicate functional dependencies.
c. Normalize the table to its 3NF, and indicate functional dependencies.
Solution to Exercise 3
Solution to Exercise 3
Exercise 4 – Functional Dependency Prove or disprove the following property using Armstrong’s Axioms Primary
Rules only.
If 𝑉 → 𝑊, 𝑉 ⊆ 𝑌 and 𝑌, 𝐾 → 𝑇, then 𝑌, 𝐾 → 𝑇, 𝑊 .
If the property is held, you have to clearly state the rules used. If a property is NOT held, disprove it by giving a counter example (a set of sample instance).
Solution to Exercise 4
If 𝑽 → 𝑾, 𝑽 ⊆ 𝒀 and 𝒀, 𝑲 → 𝑻, then 𝒀, 𝑲 → 𝑻, 𝑾 .
The property holds. The proof is as follows: (1) 𝑌,𝐾 →𝑇(Given)
(2) 𝑌, 𝐾, 𝑉 → 𝑇, 𝑉 (Augmentation of 𝑉 and (1)) (3)𝑉 → 𝑊 (Given)
(4) 𝑇, 𝑉 → 𝑇, 𝑊 (Augmentation of 𝑇 and (3)) (5) 𝑌, 𝐾, 𝑉 → 𝑇, 𝑊 (Transitivity of (2) and (4)) (6)𝑉 ⊆ 𝑌 (Given)
(7) 𝑌, 𝐾, 𝑉 = 𝑌, 𝐾
𝑉 ⊆ 𝑌, i.e., 𝑉 is part of 𝑌. Thus, 𝑌,𝑉 =𝑌.
Armstrong’s Axioms Primary Rules
i. Inclusion (Reflexive) rule: If 𝑌 ⊆ 𝑋, then 𝑋 → 𝑌.
ii. Augmentation rule: If 𝑋 → 𝑌, then 𝑊,𝑋 → 𝑊,𝑌 .
iii. Transitivityrule:If𝑋→𝑌and𝑌→𝑍,then𝑋→𝑍.
(Trivial with (6))
(8) 𝑌, 𝐾 → 𝑇, 𝑊 (Trivial with (5) and (7))
Source: metlifepetinsurance.com
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com