Database Concepts
Why Study Databases?
Databases have incredible value to civil
engineering related projects and businesses.
Very important technology for supporting operations.
Vastly superior to file processing systems.
Projects and businesses cannot survive without
quality database.
Foundation Data Concepts
Abstract Concepts
Entity – person, place, object or event
– stored as a record or a table row
Attribute – characteristic of an entity
– stored as field or table column
Foundation Data Concepts
Large
Database Concepts
Database – a collection of related tables
Tables – a collection of related records
– collection of related entities
Record – collection of fields (table row)
–represents an entity
Field – collection of characters (table column)
– represents an attribute
Character – single alphabetic, numeric or other symbol
Small
Fields
Characters “B R E I M E R” form a field
Last Name
Breimer
A field is an attribute of an entity
Records
A bunch of fields form a record
First Name Last Name Sex Weight
Eric Breimer Yes 263
A record is an entity
Tables
A bunch of records forms a table
First Name Last Name Gender Age
Eric Breimer M 30
Jeff Albert M 22
Jackie Pizzo F 21
A table is a group of related entities
Databases
A bunch of tables form a database
Customer Table Order Table
Product Table
A database can represent a single
business or an entire market
Databases
But, databases are not just a bunch of
tables Orders
OID CID PID Quantity
001 508 199 500,000
002 508 201 2
003 510 201 1
Customers Products
CID FName LName Address PID Description Cost
508 Eric Breimer ... 199 Shampoo $45.99
509 Andrew Zych ... 200 Tooth Paste $2.58
510 Greg Smith ... 201 Hair Gel $5.99
A database also includes relationships
between the different tables
Relationships & Queries in Databases
Types of Relationships
ThingA ThingB
One to One Relationship
Examples? Man Woman
Analysis Technique Married
Consider ThingA and ThingB
Can ThingA be related to more than one ThingB?
Can ThingB be related to more than one ThingA?
If the two answers are NO, then it is a one to one
relationship.
Types of Relationships
Student
Faculty Student
One to Many Student
Examples? Advises
Analysis Technique
Get Advisement
Consider ThingA and ThingB
Can ThingA be related to more than one ThingB?
Can ThingB be related to more than one ThingA?
If only one answer is yes, then you have a one to
many relationship
student takes course
Types of Relationships
Student
Course Student
Many to Many Course Student
Examples? Student
Analysis Technique
course has a student
Consider ThingA and ThingB
Can ThingA be related to more than one ThingB?
Can ThingB be related to more than one ThingA?
If the answers are yes and yes, then the relationship
is many to many.
How to Model Relationships
Orders
OID CID PID Quantity
001 508 199 500,000
002 508 201 2
003 510 201 1
Customers Products
CID FName LName Address PID Description Cost
508 Eric Breimer ... 199 Shampoo $45.99
509 Andrew Zych ... 200 Tooth Paste $2.58
510 Greg Smith ... 201 Hair Gel $5.99
Find the name and hire date of the manager working on the
sales manual project
Traditional File Processing
File Processing:
Data is organized, stored, and processed
in independent files of data records
Problems of
File Processing
Data Redundancy –
duplicate data requires
update to many files
Lack of Integration –
data stored in
separate files
hard to combine data
Data Dependence –
changing the file format requires changing the
program…
Database Management Approach
Consolidates data records into one CENTRAL
database that can be accessed by many
different application programs.
Database Management Software
(DBMS)
Definition:
Software that controls the creation,
maintenance, and use of databases
DBMS Software Components
Database Definition
Language and graphical tools to define
entities, relationships, integrity constraints,
and authorization rights
Application Development
Graphical tools to develop menus, data entry
forms, and reports
DBMS Software Components
Transaction Processing
Controls to prevent interference from
simultaneous users and
Controls to recover lost data after a failure
Database Tuning
Tools to monitor and improve database
performance
Database Interrogation
Definition:
Capability of a DBMS to report information from
the database in response to end users’ requests
Query Language – allows easy, immediate
access to data requests
Report Generator - allows quick, easy
specification of a report format for information
users have requested
Schemas
Schema - A description
Subschema – describes
of the database a subset of the database
and which users have
access to this subset
Application Development
Today, even non-technical staff can use
tools to build little programs that use a
database.
Database Management Systems have all
kinds of tools to develop custom application
programs and interfaces.
Example: The NUST (CMS) is actually an
application built on top of a Oracle
Database.
Entity Relationship
Diagram
Basic Element of ER Model
• There are three basic elements in ER models:
Entities are the "things" about which we seek
information.
• Attributes are the data we collect about the
entities.
• Relationships provide the structure needed to
draw information from multiple entities
ER Symbols
• ER diagrams often use symbols to represent
three different types of information.
• Boxes - to represent entities.
• Diamonds - to represent relationships and
• Ovals - to represent attributes.
Symbols
IGIS-NUST
How do we start an ERD?
• Define Entities: these are usually nouns used
in descriptions of the system, in the
discussion of business rules, or in
documentation; identified in the narrative
• Define Relationships: these are usually verbs
used in descriptions of the system or in
discussion of the business rules
• Add attributes to the relations; these are
determined by the queries
Relationships
• One-to-one (1:1)
• One-to-many (1:*)
• Many-to-many (*:*)
One-to-One (1:1)
Staff Branch
Manages
staffNo 1..1 0..1 branchNo
“Each branch is managed by “A member of staff can
One member of the staff” manage zero or one branch”
Many-to-Many (*:*)
Newspaper PropertyForRent
Advertises
newspaperName 0..* 1..* propertyNo
“Each property for rent is “Each newspaper advertises
advertised in zero or more one or more properties for
newspapers” rent”
Assignment
• Draw a detailed ER model diagram of student
database in MCE-CED-86 & 87. While defining
entities & relationships, write your assumptions.
• Submission Tuesday 6th Feb 2015, 1145Hrs.
