Database lesson Overview
A.2.9 Define the following database terms: table, record, field, primary key, secondary key,
foreign key, candidate key, composite primary key, join.
A.2.8 Explain the importance of data modelling in the design of a database.
A.2.4 Define the term schema.
A.2.5 Identify the characteristics of the three levels of the schema: conceptual, logical,
physical.
A.2.15 Evaluate the appropriateness of the different data types.
A.2.9 Define the following database terms: table, record,
field, primary key, secondary key, foreign key, candidate
key, composite primary key, join.
Database contains files(tables), file contains records, record contains fields,
field contains characters
What is a Table, Fields, Records
Collection of related records stored on disk
• This is a record... Group of related fields
Name Delivery Billing Phone Email Card Previous
Address Address Number Address Details Orders
Martin 1 Carr Lane 10 High St 0121 555 m.freeman VISA Samsung
Freeman 8888 @gmail.co Telly
m
• It is made up of fields... Smallest unit of data user accesses
• Field name uniquely identifies each field
• Field size defines the maximum number of characters a field can contain
• Data type specifies kind of data field contains
What is a Table, Fields, Records
Member ID First Name Last Name Address City State
2295 Milton Brewer 54 Lucy Court Shelbyville IN
2928 Shannon Murray 33099 Clark Street Montgomery AL
Records/Row/ 3876 Louella Drake 33 Timmons Place Cincinnati OH
TUPLE 3928 Adelbert Ruiz 99 Tenth Street Carmel IN
4872 Elena Gupta 2 East Penn Drive Pittsboro IN
key Fields/Coloumn/attribute
field
What is aisrecord
What a?
Group of
related fields
Key field, or primary key,
uniquely identifies each record
What is aisfield
What a?
Smallest unit of data user
accesses
Field name uniquely identifies
each field
Field size defines the maximum
number of characters a field can
contain
Data type specifies kind of data
field contains
Primary Key
• A primary key is a special relational database table column
(or combination of columns) designated to uniquely identify
all table records. A primary key's main features are: It must
contain a unique value for each row of data. It cannot
contain null values.
• A primary key, a key in a relational database that is unique
for each record. It is a unique identifier, such as a driving
license number
Foreign Key
• A foreign key is a set of one or
more columns in a table that refers
to the primary key in another
table. There isn’t any special code,
configurations, or table definitions
you need to place to officially
“designate” a foreign key.
Candidate key
• A candidate key is a column,
or set of columns, in a table
that can uniquely identify
any database record without
referring to any other data.
Each table may have one or
more candidate keys.
Secondary Key
• Only one field gets selected as primary key,
remaining keys are known as alternate or secondary keys or
indexes. They are used for searching/ indexing . A table can
have several secondary keys.
Example: Employee_ID, Employee_No and Employee_Email are the candidate keys. They uniquely
identify the Employee record. Select any one of the candidate key as the primary key. Rest of the two keys would
be Secondary Key.
Composite /Compound key
• Composite Key – A key that consists of more than one attribute to uniquely
identify rows (also known as records & tuples)
• It is a combination of two or more columns in a table that can be used to
uniquely identify each row in the table. Uniqueness is only guaranteed when
the columns are combined; when taken individually the columns do not
guarantee uniqueness
Composite /Compound key
• None of these columns alone can play a role of Primary key in this
table.
• Column cust_Id alone cannot become a key as a same customer can
place multiple orders, thus the same customer can have multiple
entires.
• Column order_Id alone cannot be a primary key as a same order can
contain the order of multiple products, thus same order_Id can be
present multiple times.
• Column product_code cannot be a primary key as more than one
customers can place order for the same product.
• Column product_count alone cannot be a primary key because two
orders can be placed for the same product count.
• Based on this, it is safe to assume that the primary key should be
having more than one attributes:
Key in above table: {cust_id, product_code}
• This is a composite key as it is made up of more than one attributes.
Join
•JOIN clause is used to: combine columns from
one or more tables in a relational database by
using values common to each. It creates a set
that can be saved as a table or used as it is.
SQL join is a Structured Query Language (SQL) instruction to combine data
from two sets of data (i.e. two tables). Examples will follow
Order Table
Customerr Table
What is the customer name who placed an
order on the 18th September 96??
To write this query we will use a JOIN
The Importance of the Data Modelling
• Data are the most basic information units employed by
a system. Applications are created to manage data and
A.2.8 Explain the to help transform data into information.
importance • data are viewed in different ways by different people.
So that there is a huge importance of data modeling in
of data modelling in DBMS.
For example, contrast the (data) view of a company
the design of manager with that of a company clerk. Although the
manager and the clerk both work for the same company,
a database the manager is more likely to have an enterprise-wide
view of company data than the clerk.
Data models can facilitate interaction • Applications programmers have yet another view of
among the designer, the applications data, being more concerned with data location,
programmer, and the end user. formatting, and specific reporting requirements.
A well-developed data model can Basically, applications programmers translate company
even foster improved understanding policies and procedures from a variety of sources into
appropriate interfaces, reports, and query screens.
of the organization for which the
database design is developed. In When a good database blueprint is available, it does not
short, data models are a matter that an applications programmer’s view of the
communication tool. data is different from that of the manager and/or the end
user. Conversely, when a good database blueprint is not
https://www.youtube.com/wat available, problems are likely to occur.
ch?v=DF0byLhClOI
Schema
A representation of a plan or theory in the form of an
outline or model:
Database Schema
A database schema is the skeleton
structure that represents the logical
view of the entire database. It
defines how the data is organized
and how the relations among them
are associated. It formulates all the
constraints that are to be applied A basic schema diagram
on the data. A database schema representing a small three-
defines its entities (tables )and the table database.
relationship among them.
Database Schema
• A database schema refer to a visual representation of a database, a
set of rules that govern a database, or to the entire set of objects
belonging to a particular user.
• Typically, a database designer creates a database schema to help
programmers whose software will interact with the database. The
process of creating a database schema is called data modeling.
• At the most basic level, a database schema indicates which tables
or relations make up the database, as well as the fields included on
each table. Thus, the terms schema diagram and entity-relationship
diagram are often interchangeable.
A.2.5 Identify the characteristics of the three levels
of the schema: conceptual, logical, physical.
There stages in designing the data Model (Schema)
1. Conceptual Model
2. Logical Model
3. Physical Data Model
Conceptual Schema
A conceptual schema is a model that
contains concepts and their relationship
between them. The concepts may be
grouped. It is a basic model on which the
database will be built.
• Highly Abstract
• Easily understood & enhanced
• Only Entities are visible
• Abstract Relationships
• No software required to create the model can
be drawn on paper or whiteboard.
Conceptual Schema Task
Create a conceptual schema for
a database that keeps record
of each student and the
subjects they have selected and
the teachers that teach that
subject.
https://www.lucidchart.com/
File From Template Data ERD
Conceptual Schema Task solution
Create a conceptual schema for a school that keeps record of each
student and the subjects they have selected and the teachers that
teach that subject.
Logical Schema
Logical schema is derived from conceptual schema. Each entity in conceptual schema is
converted to either table/field/object with any restrictions. The schema could be converted
into logical schema considering each entity as a table.
• Attribute for each entity.
• User friendly attribute names.
• More detailed then conceptual model
• At this level this model can be implemented to any database
• Software tool required to create this model
Logical Schema Task
Convert the conceptual Schema to Logical
Schema: https://www.lucidchart.com/
Attribute for each entity.
Key & non key attributes.
PK & FK relationships
User friendly attribute names.
More detailed then conceptual model
Logical Schema Task Solution
Create a logical schema for a that keeps record of each student and
the subjects they have selected and the teachers that teach that
subject.
Physical Schema
• Physical schema contains the name of the table, list of fields in each table, relationship
between tables that are specific to database management software to be used.
• Entity referred as tables.
• Attributes to columns
• Tables & Column – Database
• Entity & Attributes – logical Design
• Database compatible names (naming conventions).
• Specific datatype and length of the field
• Used for specific database
• Difficult for non-technical users to understand
• Difficult to enhance.
Physical Schema Task
Convert the Logical Schema to Physical Schema:
https://www.lucidchart.com/
• Entity referred as tables.
• Attributes to columns
• Tables & Column – Database
• Entity & Attributes – logical Design
• Database compatible names (naming conventions).
• Specific datatype and length of the field
https://www.tutorialspoint.com/postgresql/postgresql_
data_types.htm
• Used for specific database
Physical Schema Task Solution
Create a conceptual schema for a that keeps record of each student
and the subjects they have selected and the teachers that teach that
subject.
Convert Physical Schema to PostgreSQL
• Create a New Database Name it DB_Students
• Add the 4 tables with proper datatypes.
Conceptual, Logical, physical
A.2.15 Evaluate the appropriateness of the different data
types. • What are common data types?
Text Numeric AutoNumber
(also called numbers unique number automatically
alphanumeric)—letters, only assigned to each new record
numbers, or special
characters
Currency Date Memo
dollar and cent amounts or month, day, year, and lengthy text entries
numbers containing decimal sometimes time
values
Object
Yes/No Hyperlink (also called BLOB for binary large
(also called Web address that links to object)—photograph, audio, video,
Boolean)—only the document or Web page or document created in other
values Yes or No (or application such as word
True or False) processing or spreadsheet
