Introduction to Database

1
Introduction to Database

• ISYS 363

2
File Concepts

• File consists of a group of records. Each record
  contains a group of fields.
• Example Student file
• SID Sname Major Sex GPA
• S1 Peter CIS M 3.0
• S3 Paul ACCT M 2.7
• S5 Mary CIS F 3.2

3
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 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

4
XML Data with the Meaning of Data (User-Defined
Tags)
lt?xml version"1.0" ?gt ltBooksgt ltBookgt ltISBNgt1-3456
7-04-01lt/ISBNgt ltAuthorsgt ltAuthorNamegtJohn
Smithlt/AuthorNamegt ltAuthorNamegtPeter
Chenlt/AuthorNamegt ltAuthorNamegtDavid
Chaolt/AuthorNamegt lt/Authorsgt ltPricegt
45.00lt/Pricegt ltDescriptiongtThis is a grerat
booklt/Descriptiongt lt/Bookgt ltBookgt ltISBNgt1-34567-04
-02lt/ISBNgt ltAuthorsgt ltAuthorNamegtAdam
Smithlt/AuthorNamegt lt/Authorsgt ltPricegt
25.00lt/Pricegt ltDescriptiongtThis is a second
great booklt/Descriptiongt lt/Bookgt lt/Booksgt
5
Relational 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
• Sysbase, Informix, etc.

6
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.

7
Basic File Processing

• Sorting/Grouping
• Compute subtotals
• Selecting records meeting criteria

8
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 records location (address)

9
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.

10
Major Functions of an Index

• 1. Enable the direct access to a record.
• Keep track a records location on disk.
• 2. Enforce the no-duplication rule for a tables
  key field.

11
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.

12
Major Functions of Database Management

• Creating a database
• Analysis Entity-Relationship Diagram
• Design Design file structure
• Implementation
• Accessing a database
• Updating a database

13
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 tables key field plays an important role
  in linking related records.

14
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.
15
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

16
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
• 11, 1M, MM