Transcript Introduction to Database

Introduction to Database
BUS 782
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
Examples
• Database file
– Created and managed by a DBMS
• Spreadsheet file
– Created and managed by a spreadsheet software
• XML file
– Extensible Markup Language
• Comma-Delimited file (comma-separated values, CSV)– A
text file that can be processed by any programming
languages, DBMS and spreadsheet.
– "s5","peter",3.5
– "s1","paul",3
– "s7","mary",2
• Comma-Delimited file demo: stdata.txt
– Access –External Data/Text File
– Excel – Data/Get External Data/From Text
XML: Data with the Meaning of Data (UserDefined Tags)
<?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>
<AuthorName>Adam Smith</AuthorName>
</Authors>
<Price> $25.00</Price>
<Description>This is a second great book</Description>
</Book>
</Books>
Relational Database
(SQL Database)
• Data is logically structured within relations.
• Each relation is a table (file) with named
columns (attributes, fields) and rows
(records).
• Example of relational database systems:
–
–
–
–
MS Access, MS SQL Server
Oracle, MySQL
IBM DB2
Sybase, Informix, etc.
Relational Database Table
• Each table must have one key field. Key field
uniquely determines a record in a file.
• 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.
Basic File Processing
• Sorting/Grouping
• Compute subtotals
• Selecting records meeting criteria
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.
Major Functions of an Index
• 1. Enable the direct access to a record.
– Keep track a record’s location on disk.
• 2. Enforce the no-duplication rule for a
table’s key field.
Database Concepts
• A database is a collection of related files.
– Support business operations
– Provide information
• A database management system 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.
– Inconsistent data, double entry, waste space
• Link related records in related tables.
• Example:
– StudentTable: SID, Sname, Address, Major,
– StudentAccountTable: SID, Sname, Address, Balance
• Example:
– Company Database
• Department table: DID, Dname, DepOffice, DepPhone
• Employee table: EID, Ename, Address, Email, Salary
• Note: A table’s key field plays an important role
in linking related records.
An example of database design problem
Design a database for a company to keep track of
employees, departments and projects. The company is
organized into departments. Each department has a
unique number, name. Each department controls a
number of projects, each of which has a unique number,
and name. Several employees may work on the same
project. We store each employee's SSN, name, address,
salary, and birthday. An employee is assigned to one
department but may work on several projects.
Entity-Relationship Diagram
• An entity is a “thing” in the real world, such as a
person, place, event for which we intend to collect
data.
• An entity has certain characteristics (properties)
known as attributes (fields).
• Entity type(Entity set): a set of similar entities.
• A business environment may involve many entity
types.
– University: Faculty, Student, Course
– Department, Employee, Dependent
– Sales person, Customer, Order
Entity-Relationship Diagram
• 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 types of binary relationship:
– 1:1, 1:M, M:M