CS计算机代考程序代写 SQL database file system hadoop ER Excel algorithm Chapter 3

Chapter 3

Database Systems Infrastructure

This week we will be looking at relational table, and next week, we will be looking at normalisation of database tables. So, that is the red area.

INFS5710 Week 1

2
3

Recap, you have three tables instead of one gigantic table. You will learn how to do this in ERD and Normalisation which will cover in the this week and next week. We will show how to create ERD and next week we will do normalisation to ensure tables and columns are well defined.

Just a quick recap if you forgot what these tables are about: Instead of having one gigantic table, we now have three smaller and different tables namely employee, skill, and certified. Now, if you want to find which employee who did the course Basic Database Manipulation, you can find all the employees much easier using these tables.

In the Skill table, basic Database Manipulation is Skill_Id 210. Now let’s go to Certified table, Skill_id is the middle column. For 210, we have two 06234 and 09382. Now, we can go to Employee table; you can see 06234 is Jasmine Patel and, 09382 is Jessica Johnson.

If you want an easier way, you will write SQL statements to link these tables together to get the results. This is something you can look forward to doing in the lab.

INFS5710 Week 2

Table 3.1
Characteristics of a Relational Table
1 A table is perceived as a two-dimensional structure composed of rows and columns.
2 Each table row (tuple) represents a single entity occurrence within the entity set.
3 Each table column represents an attribute, and each column has a distinct name.
4 Each intersection of a row and column represents a single data value.
5 All values in a column must conform to the same data format.
6 Each column has a specific range of values known as the attribute domain.
7 The order of the rows and columns is immaterial to the DBMS.
8 Each table must have an attribute or combination of attributes that uniquely identifies each row.

relation = table
row = tuple = “entity”
column = attribute
 called “primary key”

©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
.
Okay, firstly, we look at a relational table. Let’s look at Table 3.1 it describes characteristics of a table. A table is like a 2-dimensional spreadsheet containing rows and columns. The sheet tab in Excel is like a table in relational database. The row is the tuple, and column is the attribute, and the cell contains the data.

For each column, like in Excel, it has to be the same type of data. For example, if a column is a date, then the whole column must be all dates. If a column is numeric, then the whole column must be numeric.

For the last point, number 8, each table must have an attribute or combination of attributes that uniquely identifies each row. We also called this Primary key, which we will discuss later.

INFS5710 Week 2

Keys
Consist of one or more attributes that determine other attributes
Used to:
Ensure that each row in a table is uniquely identifiable
Establish relationships among tables and to ensure the integrity of the data
Primary key (PK): Attribute or combination of attributes that uniquely identifies any given row

Very Important
A PK may contain more than one attribute.
For example, STUDENTS table, the PK is SID; EMPLOYEES table, PK is employee ID.

So, whenever you fill in a form at the university or login using your student id, the system knows it is you. Thus, student id is a primary key and uniquely identifies you and you only.

In a S table (say) in the university system, a column ZId will be the primary key of that table. If your student id is 1234567, and if someone tries to enter a new record with ZId 1234567, it will be rejected because it is already existed in the table. Other examples include your driving license, and your tax file number.

In the definition of primary key, it can be uniquely identified by one or more columns. This means you can have one, two, three, four or even more columns combine to form a primary key.

INFS5710 Week 2

Find the Primary Keys
Consider the following tables pertaining to your course enrolment:
Students
SID
Email
Title
Surname
First Name
DOB
Home Address

Courses
Course ID
Course Name

Class Enrolment
SID
Course ID
Class ID
Term
Delivery Mode
Instructor

INFS5710 Week 2

Dependencies
Determination
State in which knowing the value of one attribute makes it possible to determine the value of another
Establishes the role of a key
Based on the relationships among the attributes
Functional dependence: value of one or more attributes determines the value of one or more other attributes
Determinant: attribute whose value determines another
Dependent: attribute whose value is determined by the other attribute
Full functional dependence: entire collection of attributes in the determinant is necessary for the relationship

STU_NUM  STU_LNAME
(determinant) (dependent)
e.g., PK
STU_NUM  STU_LNAME
to search along the corresponding “row”
(STU_NUM, STU_LNAME)  STU_GPA ?

©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
.
Determination is the state in which knowing the value one attribute makes it possible to determine the value of another. The idea of determination not unique to the database outright. For example, Profit = revenue minus costs. As long as you know the revenue and costs, you can determine the profit. Determination in a database environment, however, is not normally based on a formula but on the relationships among the attributes. For example, if I know the student number, I can find out the student name.

Functional dependence is when the value of one or more attributes determines The value of one or more other attributes. For example, student number and student name is shown here.

INFS5710 Week 2

Types of Keys (1)
Composite key: Key that is composed of more than one attribute
Key attribute: Attribute that is a part of a key
Superkey: key that can uniquely identify any row in the table
Candidate key: minimal superkey
Entity integrity: Condition in which each row in the table has its own unique identity
All of the values in the primary key must be unique
No key attribute in the primary key can contain a null

For example, the course enrolment table has ZId, course_id, and term_id is a composite key
(A, B)  C
(A, B)
A, B

Key attribute is when an attribute that is a part of a key.

A Superkey is a key that can uniquely identify any row in the table. For example, Course_Id in Courses table, ZID in Students table, and ZId, term_id, and course_id for course_enrolment table.

A candidate key is a minimal superkey. For example, in the Student table, your mobile number might able to identify who you are. Sometimes, when you go a shop, you might forget your rewards card, they might ask you for your mobile number.

Entity integrity is when a condition in which each row in the table has its own unique identity. All of the values in the primary key must be unique and no key attribute in the primary key can be null.

INFS5710 Week 2

Types of Keys (2)
Null: Absence of any data value that could represent:
An unknown attribute value
A known, but missing, attribute value
An inapplicable condition
Foreign key (FK): primary key of one table that has been placed into another table to create a common attribute
Referential integrity: Every reference to an entity instance by another entity instance is valid
Secondary key: Key used strictly for data retrieval purposes

(e.g., people do not remember their membership no. (PK),
the secondary key can be their name, which may not be unique.)

Referential integrity is when you want to ensure, for example, before you add a record with a ZID, you need to ensure the ZID exists in the students table.

A Foreign key (FK) is when you associate one attribute of an entity to an attribute of another entity.

Secondary key is the key used specifically for data retrieval.

INFS5710 Week 2

Figure 3.2 – An Example of a Simple Relational Database

used to link to some other table. A foreign key
must be the PK of some other table.
this table is self-sufficient.

INFS5710 Week 2

Table 3.3 – Relational Database Keys

e.g., the set of all attributes

INFS5710 Week 2

Integrity Rules

e.g., STUDENT.supervisor = “null” (not yet assigned)

INFS5710 Week 2

Find the Foreign Keys
Consider the following tables pertaining to your course enrolment:
Students
SID
Email
Title
Surname
First Name
DOB
Home Address

Courses
Course ID
Course Name

Class Enrolment
SID
Course ID
Class ID
Term
Delivery Mode
Instructor

INFS5710 Week 2

Relationships within the Relational Database
1:M relationship – Norm for relational databases
1:1 relationship – One entity can be related to only one other entity and vice versa
Many-to-many (M:N) relationship – Implemented by creating a new entity in 1:M relationships with the original entities
Composite entity (Bridge or associative entity): Helps avoid problems inherent to M:N relationships, includes the primary keys of tables to be linked

INFS5710 Week 2

Figure 3.19 – The 1:M Relationship between COURSE and CLASS

Course 1
Course 2
Course 3
Course 4
Course 1, Class 1
Course 1, Class 2
Course 1, Class 3
Course 2, Class 1
Course 3, Class 1
:
FK

INFS5710 Week 2

Figure 3.21 – The 1:1 Relationship between PROFESSOR and DEPARTMENT

This implied that DEPARTMENT has an attribute for department chair.
Professor 1
Professor 2
Professor 3
Professor 4
Dept 1, Professor X as chair
Dept 2, Professor Y as chair
Dept 3, Professor Z as chair
Dept 4, Professor W as chair
FK

INFS5710 Week 2

Figure 3.26 – Changing the M:N Relationship to Two 1:M Relationships

Class X1, student Y1
Class X1, student Y2
:
Class X2, student Y3
Class X2, student Y4
:
Let each student appear only
one time in STUDENT table.
Let each class appear only
one time in CLASS table.
composite
entity
Student Y1, class X1
Student Y1, class X2
:
Student Y2, class X1
Student Y2, class X3
:

We will cover more later.

INFS5710 Week 2

Data Redundancy Revisited
Relational database facilitates control of data redundancies through use of foreign keys
To be controlled except the following circumstances
Sometimes data redundancy must be increased to make the database serve crucial information purposes
Sometimes data redundancy exists to preserve the historical accuracy of data

Entity Relationship Model (ERM)
Basis of an entity relationship diagram (ERD)
ERD depicts the:
Conceptual database as viewed by end user
Database’s main components
Entities
Attributes
Relationships
Entity – Refers to the entity set and not to a single entity occurrence
(Tables)
(Columns of tables)
(associations between tables)

It mainly has three components, namely Entities, attributes, and relationships.

We will cover in class but please read the textbook.

INFS5710 Week 2

Attributes (1)
Characteristics of entities
Required attribute: Must have a value, cannot be left empty
Optional attribute: Does not require a value, can be left empty
Domain: Set of possible values for a given attribute
Identifiers: One or more attributes that uniquely identify each entity instance

called Keys in the relational model

Figure 4.1 – The Attributes of the Student Entity: Chen and Crow’s Foot

boldfaced attributes are required and cannot be null.

We will focus on using Crow’s Foot Model because it uses in the textbook. Why does it call Crow’s Foot because it looks a crow foot (see diagram) 😊

INFS5710 Week 2

Attributes (2)
Composite identifier: Primary key composed of more than one attribute
Composite attribute: Attribute that can be subdivided to yield additional attributes
Simple attribute: Attribute that cannot be subdivided
Single-valued attribute: Attribute that has only a single value
Multivalued attributes: Attributes that have many values

e.g., name – John F. Kennedy;
DOB – 29/5/1917
e.g., gender – Male
e.g., diploma – BS, MS, MBA, etc.
e.g., gender – Male

(think about the ENROLL table on slide 18.)
(composite key)

INFS5710 Week 2

Figure 4.3 – A Multivalued Attribute in an Entity

car’s vehicle identification number (VIN)

multiple values

The car can have multiple values, i.e. a car can have different colours, for example, looking at this car, you can have red, white, and black.

INFS5710 Week 2

Attributes
Multivalued attributes: Attributes that have many values and require creating:
Several new attributes, one for each component of the original multivalued attribute
A new entity composed of the original multivalued attribute’s components
Derived attribute: Attribute whose value is calculated from other attributes
Derived using an algorithm

e.g., age. There seems no reason that a
database needs to store one’s age.

INFS5710 Week 2

Figure 4.4 – Splitting the Multivalued Attributes into New Attributes

Is this a good idea?

However, is this a good idea?

What’s happens if you have another colour on the car handle, another colour for the bumper, another colour for the screen wiper, and so on? So, are you going to add more attributes?

This is all depends on how you design.

INFS5710 Week 2

Figure 4.6 – Depiction of a Derived Attribute

Do we need emp_age?

Technically, we do not store the employee’s age because we can calculate from date of birth of the employee. Otherwise, you have to update the age every day.

However, sometimes because of the nature of the business, you might need to do the calculation upfront in order to speed up the data retrieval. For example, on Alibaba’s 11/11 Single’s Day in China, similar to Black Friday sales or Cyber Monday sales, in 2019, it has a record of US$38 billion in sales in just one day!

That was the biggest sales in just one day. Imagine you can get 1% of the sales, i.e. $380 million.

There are plenty of orders going through every second, so you have to make sure you get those orders, you do not want your customer to wait, that is the important factor. You do not want to waste time doing calculation, such as the unit promotion price, for example. So, you might in this case want to add the unit price to the entity or table. This is exception rather than norm as it heavily relies on the business rules.

INFS5710 Week 2

Table 4.2 Advantages and Disadvantages of Storing Derived Attributes
Derived Attribute: Stored Derived Attribute: Not Stored
Advantage Saves CPU processing cycles
Saves data access time
Data value is readily available
Can be used to keep track of historical data Saves storage space
Computation always yields current value
Disadvantage Requires constant maintenance to ensure derived value is current, especially if any values used in the calculation change Uses CPU processing cycles
Increases data access time
Adds coding complexity to queries

Time vs. Storage

INFS5710 Week 2

Relationships
Association between entities that always operate in both directions
Participants: Entities that participate in a relationship
Connectivity: Describes the relationship classification
Cardinality: Expresses the minimum and maximum number of entity occurrences associated with one occurrence of related entity

tables
e.g., how many diplomas at most one can enter to the table.
(“relationship” previously)
1:1, 1:M, and M:N
(min, max)

INFS5710 Week 2

Figure 4.7 – Connectivity and Cardinality in an ERD

by “Business Rule”:
each professor teaches at least one class, but
no more than 4.
1:M
(min, max)

Usually, if you do not know the maximum classes professor can teach, then you can just put a capital M. This is very common in the case of ERDs.

INFS5710 Week 2

Table 4.3 – Crow’s Foot Symbols

optional vs. mandatory

Min = 0
Min = 1

INFS5710 Week 2

Figure 4.13 – CLASS is Optional to COURSE

Read the textbook on explaining why this is optional!

INFS5710 Week 2

Figure 4.14 – COURSE and CLASS in a Mandatory Relationship

Read the textbook on explaining why this is a mandatory relationship!

I know what the book said (Fig 4-13 is more likely the case), but in long term, a database designer is more likely to use figure 4.14 to ensure when you have course, you have a class. There are ways to get around this, please see next slide.

INFS5710 Week 2

Associative (Composite) Entities
Used to represent an M:N relationship between two or more entities
Has a 1:M relationship with the parent entities
Composed of the primary key attributes of each parent entity
May also contain additional attributes that play no role in connective process

Many to Many Relationship

Below shows that many students enrol in many classes. However, this cannot be implemented.

If you have one in your ERD, and you did not resolve, then you will face a big problem!

INFS5710 Week 2

Converting the M:N Relationship into Two 1:M Relationships

Normally, the primary key of the new entity is made up of a composite key. In this case, class code and student number combine to form a composite key for enroll entity.

Also, have a look at the tables…

INFS5710 Week 2

Developing an ER Diagram
Create a detailed narrative of the organization’s description of operations
Identify business rules based on the descriptions
Identify main entities and relationships from the business rules
Develop the initial ERD
Identify the attributes and primary keys that adequately describe entities
Revise and review ERD

INFS5710 Week 2

Figure 4.26 – The First Tiny College ERD Segment

dotted line means
weak (non-identifying)
relationship, see 4-1f
in the textbook
A professor may be the dean of 0 school.

That is, a Weak (non-identifying) relationship is when a primary key of the related entity does not contain a primary key component of the parent entity. Read the textbook for explanations.

Database designer determines whether an entity is weak based on business rules. However, when it comes to implementation, it just becomes tables.

INFS5710 Week 2

Figure 4.27 – The Second Tiny College ERD Segment

A department may offer 0 course

INFS5710 Week 2

Figure 4.28 – The Third Tiny College ERD Segment

INFS5710 Week 2

Figure 4.29 – The Fourth Tiny College ERD Segment

INFS5710 Week 2

Figure 4.30 – The Fifth Tiny College ERD Segment

INFS5710 Week 2

Figure 4.31 – The Sixth Tiny College ERD Segment

Strong (identifying) relationship

©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
.
This is an example of Strong (identifying) relationships, which is when a Primary key of the related entity contains a primary key component of the parent entity, i.e. Student number in Enroll entity associates with student number from Student table, and class code in in Enroll entity associates with class code from class entity.

INFS5710 Week 2

Figure 4.32 – The Seventh Tiny College ERD Segment

INFS5710 Week 2

Figure 4.33 – The Eighth Tiny College ERD Segment

INFS5710 Week 2

Figure 4.34 – The Ninth Tiny College ERD Segment

INFS5710 Week 2

Oracle
Oracle
Database
Flat Files
Machine Learning
Prepared by Vincent Pang, Feb. 2021 Note: In-Memory Database (e.g. SAP Hana) is an alternative data model not shown here
ETL (Data
Cleansing)
Data (DW to BD, or
vice versa or both)
RelationalDatabaseBig DataDataWarehouseNormalisationReporting (Business Intelligence and Visualisation) and Business Analysis (End Users)(De-Normalised)External Data (e.g. Excel)Hadoop Distributed File System (HDFS) and MapReduceEntity Relationship Model (ERM)Unstructured Data(Social Media)Structured Data (Internet of Things (IOT))HadoopSQLSQLSpark and NoSQL (and other tools)Data StreamingData Streaming(“Not Normalised”)External Data (e.g. Excel)Star Schema

Related Posts