Transcript Introduction to Database - San Francisco State University

Introduction to Database
File Formats
• Comma delimited file
–
–
–
–
"s1","peter",3
"s2","paul",2.5
"s3","mary",3.5
Demo: Excel – Data/Import
• Extended Markup Language (XML) document
• Spreadsheet list
• Database table
XML
<?xml version="1.0" ?>
<Books>
<Book>
<ISBN>1-34567-04-01</ISBN>
<Authors>
<AuthorName>John Smith</AuthorName>
<AuthorName>Peter Chen</AuthorName>
<AuthorName>David Chao</AuthorName>
</Authors>
<Price> $45.00</Price>
<Description>This is a grerat book</Description>
</Book>
<Book>
<ISBN>1-34567-04-02</ISBN>
<Authors>Adam Smith</Authors>
<Price> $25.00</Price>
<Description>This is a second great book</Description>
</Book>
</Books>
File Concepts
• File consists of a group of records. Each
record contains a group of fields.
• Example: Student file
–
–
–
–
SID
S1
S3
S5
Sname
Peter
Paul
Mary
Major
CIS
ACCT
CIS
Sex
M
M
F
GPA
3.0
2.7
3.2
File Concepts
• Each file must have one key field. Key field
uniquely determines a record in a file.
– Simple key, composite key
• Grouping field: A filed that can be used to
group records, for example, Major in the
Student file.
• Calculated filed: A field whose value is
derived from existing fields, for example,
Age can be calculated from DateOfBirth.
• Each field can hold only one value.
Calculated Field Example (1)
• DateOfBirth -> Age
• Date functions:
–
–
–
–
Now
Today
Year
Etc.
• Age =
Calculated Field Example (2)
• Salary field -> IncomeGroup
– Salary<=40000, -> Low
– 40000 <Salary <=70000, -> Median
– Salary > 70000, -> High
Which field is the key field?
File Organization and Access
• Sequential file (heap file): Records are
organized in sequence in the order in which
they are entered.
• File access:
– Sequential access
– Direct access
• Index file: Key + record’s location (address)
Direct access time
• 1. Seek time: This is the time needed to
mechanically position the read/write head on
the correct track.
• 2. Rotational delay: This is the time needed to
wait for the beginning of the required block to
rotate into position under the read/write head
• 3. Data transfer time: Time needed to transfer
the data.
Database Concepts
• A database is a collection of related files.
– Support business operations
– Provide information
Database Management System
• A database management system (DBMS) is
a software that enables users to create and
maintain a database.
Major Functions of Database
Management
• Creating a database
– Analysis: Entity-Relationship Diagram
– Design: Design file structure
– Implementation
• Accessing a database
• Updating a database
Database design objectives
• Eliminate data duplication. Example:
– Student file: SID, SNAME, ADDR, MAJOR,
GPA
– Student Account File: SID, SNAME, ADDR,
BALANCE
• Link related records in the related tables.
An Example of Database
• Department, Employee:
– Each department has many employee, and each
employee works for one department.
• Design a database so that we can:
– Find which department David Chao works for.
– Find all the employees that work for the
Personnel department.
Entity-Relationship Diagram
• Entity (Entity set): a set of similar objects.
• A business environment may involve many
entities.
– University: Faculty, Student, Course
– Customer, Employee, Order
• A business operation may involve more than
one entities.
– Faculty teach Course, Faculty advise Student
– Customer open Account, Customer purchase
Product.
Relationship
• Relationship: Interaction between entity
types.
– Faculty teach Course, Faculty advise Student
– Customer open Account, Customer purchase
Product.
• Binary relationship: A relationship involves
two entity types.
Three kinds of Binary Relationship
• 1:1
– Each student has one account
– Each account belongs to one student
• 1:M
– Each college has many departments
– Each department belongs to one college
• M:M
– Each student can take many courses
– Each course can be taken by may students
Student
1
Has
1
Account
M
Enroll
M
Course
Faculty
Department
Attributes
• Properties of an entity or a relationship.
– Student: SID, Sname, DateOfBirth
• Primary key
• Derived attributes:
– Age
SID
Sname
Phone
DateOfBirth
Age
Student
Database Design Demo
• Each entity translates to a table.
• Relationships are implemented by foreign
key or relationship table.
• Example 1: University database
• Example 2: Customer/Orders
Implementation with Access
• Access objects:
– Tables
•
•
•
•
–
–
–
–
Open
Design
New
Wizard
Queries
Forms
Reports
Pages
Creating a Table
• Create table in design view
– Field name
– Field data type
• Create table by using wizard
• Create a primary key
• Enter data
Querying Database