|
|
DATABASE CONCEPTS
Chapter outline
- 1.1 What is a database and a DBMS?
- 1.2 Why do we need DBMS?
- 1.3 Architecture of DBMS
- 1.4 People who work with database
- 1.5 Database languages and interfaces
What is a database and a DBMS?
Database is a collection of related data which represents some aspect of the real world
ex : a university database
- Entities ex : student, faculty, course, classroom
- Relationships between entities ex : students’s enrollment in courses, faculty teaching courses, the use of rooms for courses
What is a DBMS?
- A database management system is software designed to assist in maintaining and utilizing large collections of data.
- DBMS enables users to define, create, and maintain the database and provides controlled access to the database.
Traditional file-based systems
- File-based system - A collection of application programs that performs services for the end-users such as the production of reports. Each program defines and manages its own data.
- Limitations of file-based systems
- Seperation and isolation of data
- Duplication of data
- Data dependence (Program-data dependence)
- Fixed queries/proliferation of application program
Database approach
- A database is a single, large repository of data, which is defined once and used simultaneously by many departments and users.
- Advantages of a DBMS
- Data independence
- Efficient data access
- Data integrity and security
- Data administration
- Concurrent access and crash recovery
- Reduced application development time
Describing and storing data in a DBMS
- A data model is a collection of high-level data description constructs that hide many low-level storage details.
- network data model
- hierarchical data model record-based data model
- relational data model
- object data model
- We will study only relational data model that uses ER model to describe entities and relationships among entities
The relational data model
- Relation (table) = A set of records (instance)
- A description of data in terms of data model is called a schema.
- Database schema
- Relation schema specifies its name, the name of each field (attribute, column), and the type of each field. Ex :
student (sid : string, name : string, login : string, age : integer, gpa : real)
The relational data model(Contd.)
- Instance is the collection of information stored in the database at a particular moment
- Ex : instance of student relation
sid name login age gpa
53666 Jones jones@cs 18 3.4
53688 Smith smith@ee 18 3.2
53831 Madayan madayan@ma 11 1.8
*** instance : frequently changed
*** schema : hardly changed
Properties of relations
- The relation has a name that is distinct from all other relation names
- Each cell of the relation contains exactly one atomic (single) value
- Each attribute has a distinct name
- The values of an attribute are all from the same domain
Properties of relations (Contd.)
- The order of attributes has no significance
- Each tuple is distinct; there are no duplicate tuples
- The order of tuples has no significance, theoretically. (However, in practice, the order may affect the efficiency of accessing tuples)
DBMS architecture
DBMS architecture (Contd.)
- Physical level - The lowest level of abstraction describes how the data are actually stored. At the physical level, complex low-level data structures are described in detail. We must decide what file organizations to use to store the relations, and create auxiliary data structure called indexes to speed up data retrieval operation.
- Logical level - The next higher level of abstraction describes what data are stored in the database, and what relationships exist among those data.
- External level - The highest level of abstraction describes only part of the entire database. Users need to access only a part of the database. So that their interaction with the system is simplified.
Ex : conceptual schema of the university database
student (sid : string, name : string, login : string, age : integer, gpa : real)
faculty (fid : string, fname : string, sal : real)
course (cid : string, cname : string, credits : integer)
room (rno : integer, address : string, capacity : integer)
enrolled (sid : string, cid : string, grade : string)
teaches (fid : string, cid : string)
meet_in (cid : string, rno : integer, time :string)
Ex : external schema of courseinfo
courseinfo (cid : string, fname : string, enrollment : integer)
Data Independence
- Logical data independence is the capacity to change the conceptual schema without having to change external schemas or application programs
- Physical data independence is the capacity to change the internal schema without having to change the conceptual or external schema.
Database languages and interfaces
DBMS language
1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
DBMS interfaces
1. Form-based interface
2. GUI
3. Command-line interface
|
