Surasit com Database Fundamental Design by A

Database Is an integrated collection of logically related records or files that is stored in a computer system which consolidates records previously stored in separate files into a common pool of data records that provides data for many applications A database is a collection of information that is organized so that it can easily be accessed, managed, and updated Databases can be classified according to types of content: bibliographic, full-text, numeric, and images The structure is achieved by organizing the data according to a database model. The most commonly used model today is the relational model Page 2

Database Management System (DBMS) Collection of interrelated data Set of programs to access the data DBMS contains information about a particular enterprise DBMS provides an environment that is both convenient and efficient Page 3

Database Applications Databases touch all aspects of our lives Database Applications: • Banking: all transactions • Airlines: reservations, schedules • Universities: registration, grades • Sales: customers, products, purchases • Manufacturing: production, inventory, orders, supply chain • Human resources: employee records, salaries, tax deductions Page 4

DBMS Features and Capabilities Query ability • Querying is the process of requesting attribute information from various perspectives and combinations of factors. Example: "How many 2 -door cars in Texas are green? " A database query language and report writer allow users to interactively interrogate the database, analyze its data and update it according to the users privileges on data Backup and replication • Copies of attributes need to be made regularly in case primary disks or other equipment fails. A periodic copy of attributes may also be created for a distant organization that cannot readily access the original. DBMS usually provide utilities to facilitate the process of extracting and disseminating attribute sets. When data is replicated between database servers, so that the information remains consistent throughout the database system and users cannot tell or even know which server in the DBMS they are using, the system is said to exhibit replication transparency Page 5

DBMS Features and Capabilities Rule enforcement • Often one wants to apply rules to attributes so that the attributes are clean and reliable. For example, we may have a rule that says each car can have only one engine associated with it (identified by Engine Number). If somebody tries to associate a second engine with a given car, we want the DBMS to deny such a request and display an error message. However, with changes in the model specification such as, in this example, hybrid gaselectric cars, rules may need to change. Ideally such rules should be able to be added and removed as needed without significant data layout redesign Security • Often it is desirable to limit who can see or change which attributes or groups of attributes. This may be managed directly by individual, or by the assignment of individuals and privileges to groups, or (in the most elaborate models) through the assignment of individuals and groups to roles which are then granted entitlements Page 6

DBMS Features and Capabilities Computation • There are common computations requested on attributes such as counting, summing, averaging, sorting, grouping, cross-referencing, etc. Rather than have each computer application implement these from scratch, they can rely on the DBMS to supply such calculations Change and access logging • Often one wants to know who accessed what attributes, what was changed, and when it was changed. Logging services allow this by keeping a record of access occurrences and changes Automated optimization • If there are frequently occurring usage patterns or requests, some DBMS can adjust themselves to improve the speed of those interactions. In some cases the DBMS will merely provide tools to monitor performance, allowing a human expert to make the necessary adjustments after reviewing the statistics collected Page 7

Examples of Database Management Systems Adabas, Adaptive Server Enterprise, Alpha Five, Computhink's View. Wise, CSQL, Daffodil DB, Data. Ease, File. Maker, Firebird, Glom, IBM DB 2, IBM Uni. Verse, Ingres, Informix, Inter. Systems Caché, Kexi, WX 2, Linter SQL RDBMS, Mark Logic, Microsoft Access, Microsoft SQL Server, Microsoft Visual Fox. Pro, Monet. DB, My. SQL, Open. Link Virtuoso, Open. Office. org Base, Oracle Database, Par. Accel, Postgre. SQL, Progress, SQL Anywhere, SQLite, Teradata, Vertica Analytic Database Page 8

Data Definition Language (DDL) Specification notation for defining the database schema create table account ( account-number char(10), balance integer) DDL compiler generates a set of tables stored in a data dictionary Data dictionary contains metadata (i. e. , data about data) • Database schema • Data storage and definition language – Language in which the storage structure and access methods used by the database system are specified – Usually an extension of the data definition language Page 9

Database Schema The schema of a database system is its structure described in a formal language supported by the database management system (DBMS) In a relational database, the schema defines the tables, the fields in each table, and the relationships between fields and table Schemas are generally stored in a data dictionary. Although a schema is defined in text database language, the term is often used to refer to a graphical depiction of the database structure Page 10

Database Schema Table Database schema table contains • Table name and its columns – Begins with the title of database table and list all columns from the table in the blanket with its relationship • Attribute – Column’s name • Domain name – Short word to describe the attribute • Meaning – Explains what the attribute do • Domain Definition – Definition of attribute with type and its size Page 11

Database Schema Example Page 12

Data Manipulation Language (DML) Language for accessing and manipulating the data organized by the appropriate data model • DML also known as query language Two classes of languages • Procedural – user specifies what data is required and how to get those data • Nonprocedural – user specifies what data is required without specifying how to get those data Page 13

Database Query Language A database query language and report writer allows users to interactively interrogate the database, analyze its data and update it according to the users privileges on data It also controls the security of the database. Data security prevents unauthorized users from viewing or updating the database. Using passwords, users are allowed access to the entire database or subsets of it called subschemas For example, an employee database can contain all the data about an individual employee, but one group of users may be authorized to view only payroll data, while others are allowed access to only work history and medical data Page 14

Database Query Language If the DBMS provides a way to interactively enter and update the database, as well as interrogate it, this capability allows for managing personal databases. However, it may not leave an audit trail of actions or provide the kinds of controls necessary in a multi-user organization. These controls are only available when a set of application programs are customized for each data entry and updating function SQL is the most widely used query language SQL stands for Structured Query Language Page 15

Data Models A collection of tools for describing • data relationships • data semantics • data constraints Relational model Entity-Relationship model Other models: • object-oriented model • semi-structured data models • Older models: network model and hierarchical model Page 16

Relational Database Model Page 17

Entity-Relationship Model Is an abstract and conceptual representation of data Is a database modeling method, used to produce a type of conceptual schema or semantic data model of a system, often a relational database, and its requirements in a top-down fashion Widely used for database design – Database design in E-R model usually converted to design in the relational model which is used for storage and processing Diagrams created using this process are called entity-relationship diagrams, or E-R diagrams, or ER diagrams or ERDs for short Page 18

E-R Diagram Rectangles represent entity sets. Diamonds represent relationship sets. Lines link attributes to entity sets and entity sets to relationship sets. Ellipses represent attributes – Double ellipses represent multi-valued attributes – Dashed ellipses denote derived attributes Underline indicates primary key attributes Page 19

E-R Diagram Example Page 20

E-R Diagram for the Banking Enterprise Page 21

E-R Diagram Example Class Enrollment Car Insurance Page 22

Entity Sets A database can be modeled as: • a collection of entities and relationship among entities An entity is an object that exists and is distinguishable from other objects. • Example: specific person, company, event, plant Entities have attributes • Example: people have names and addresses An entity set is a set of entities of the same type that share the same properties • Example: set of all persons, companies, trees, holidays Page 23

Attributes An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set Domain – the set of permitted values for each attribute Attribute types: • Simple and composite attributes • Single-valued and multi-valued attributes – E. g. multi-valued attribute: phone-numbers • Derived attributes – Can be computed from other attributes – E. g. age, given date of birth Page 24

Relationship Sets A relationship is an association among several entities A relationship set is a mathematical relation among entities, each taken from entity sets Page 25