Database System Architecture 

Cours Database System Architecture, tutoriel & guide de travaux pratiques en pdf.

Basic Concepts

The E-R data model employs three basic notions: entity sets, relationship sets, and attributes.

Entity Sets

An entity is a “thing” or “object” in the real world that is distinguishable from all other objects. For example, each person in an enterprise is an entity. An entity has a set of properties, and the values for some set of properties may uniquely identify an entity. For instance, a person may have a person-id property whose value uniquely identifies that person. Thus, the value 677-89-9011 for person-id would uniquely identify one particular person in the enterprise. Similarly, loans can be thought of as entities, and loan number L-15 at the Perryridge branch uniquely identifies a loan entity.
An entity may be concrete, such as a person or a book, or it may be abstract, such as a loan, or a holiday, or a concept.
An entity set is a set of entities of the same type that share the same properties, or attributes. The set of all persons who are customers at a given bank, for example, can be defined as the entity set customer. Similarly, the entity set loan might represent the set are said to be the extension of the entity set. Thus, all the individual bank customers are the extension of the entity set customer.
Entity sets do not need to be disjoint. For example, it is possible to define the entity set of all employees of a bank (employee) and the entity set of all customers of the bank (customer). A person entity may be an employee entity, a customer entity, both, or neither.
An entity is represented by a set of attributes. Attributes are descriptive properties possessed by each member of an entity set. The designation of an attribute for an entity set expresses that the database stores similar information concerning each entity in the entity set; however, each entity may have its own value for each attribute.
Possible attributes of the customer entity set are customer-id, customer-name, customerstreet, and customer-city. In real life, there would be further attributes, such as street number, apartment number, state, postal code, and country, but we omit them to keep our examples simple. Possible attributes of the loan entity set are loan-number and amount.
Each entity has a value for each of its attributes. For instance, a particular customer entity may have the value 321-12-3123 for customer-id, the value Jones for customername, the value Main for customer-street, and the value Harrison for customer-city.
The customer-id attribute is used to uniquely identify customers, since there may be more than one customer with the same name, street, and city. In the United States, many enterprises find it convenient to use the social-security number of a person1 as an attribute whose value uniquely identifies the person. In general the enterprise would have to create and assign a unique identifier for each customer.
For each attribute, there is a set of permitted values, called the domain, or value set, of that attribute. The domain of attribute customer-name might be the set of all text strings of a certain length. Similarly, the domain of attribute loan-number might be the set of all strings of the form “L-n” where n is a positive integer.
A database thus includes a collection of entity sets, each of which contains any number of entities of the same type. Figure 2.1 shows part of a bank database that consists of two entity sets: customer and loan.
Formally, an attribute of an entity set is a function that maps from the entity set into a domain. Since an entity set may have several attributes, each entity can be described by a set of (attribute, data value) pairs, one pair for each attribute of the entity set. For example, a particular customer entity may be described by the set {(customer-id, 677-89-9011), (customer-name, Hayes), (customer-street, Main), (customer-city, Harrison)}, meaning that the entity describes a person named Hayes whose customer identifier is 677-89-9011 and who resides at Main Street in Harrison. We can see, at this point, an integration of the abstract schema with the actual enterprise being modeled. The
attribute values describing an entity will constitute a significant portion of the data stored in the database.
An attribute, as used in the E-R model, can be characterized by the following attribute types.
• Simple and composite attributes. In our examples thus far, the attributes have been simple; that is, they are not divided into subparts. Composite attributes, on the other hand, can be divided into subparts (that is, other attributes). For example, an attribute name could be structured as a composite attribute consisting of first-name, middle-initial, and last-name. Using composite attributes in a design schema is a good choice if a user will wish to refer to an entire attribute on some occasions, and to only a component of the attribute on other occasions. Suppose we were to substitute for the customer entity-set attributes customer-street and customer-city the composite attribute address with the attributes street, city, state, and zip-code.2 Composite attributes help us to group together related attributes, making the modeling cleaner.
Note also that a composite attribute may appear as a hierarchy. In the composite attribute address, its component attribute street can be further divided into street-number, street-name, and apartment-number. Figure 2.2 depicts these examples of composite attributes for the customer entity set.
• Single-valued and multivalued attributes. The attributes in our examples all have a single value for a particular entity. For instance, the loan-number attribute for a specific loan entity refers to only one loan number. Such attributes are said to be single valued. There may be instances where an attribute has a set of values for a specific entity. Consider an employee entity set with the attribute phone-number. An employee may have zero, one, or several phone numbers, and different employees may have different numbers of phones.
This type of attribute is said to be multivalued. As another example, an attribute dependent-name of the employee entity set would be multivalued, since any particular employee may have zero, one, or more dependent(s).
Where appropriate, upper and lower bounds may be placed on the number of values in a multivalued attribute. For example, a bank may limit the number of phone numbers recorded for a single customer to two. Placing bounds in this case expresses that the phone-number attribute of the customer entity set may have between zero and two values.
• Derived attribute. The value for this type of attribute can be derived from the values of other related attributes or entities. For instance, let us say that the customer entity set has an attribute loans-held, which represents how many loans a customer has from the bank.We can derive the value for this attribute by counting the number of loan entities associated with that customer.
As another example, suppose that the customer entity set has an attribute age, which indicates the customer’s age. If the customer entity set also has an attribute date-of-birth, we can calculate age from date-of-birth and the current date. Thus, age is a derived attribute. In this case, date-of-birth may be referred to as a base attribute, or a stored attribute. The value of a derived attribute is
not stored, but is computed when required.
An attribute takes a null value when an entity does not have a value for it. The null value may indicate “not applicable”—that is, that the value does not exist for the entity. For example, one may have no middle name. Null can also designate that an attribute value is unknown. An unknown value may be either missing (the value does exist, but we do not have that information) or not known (we do not know whether or not the value actually exists).
For instance, if the name value for a particular customer is null, we assume that the value is missing, since every customer must have a name. A null value for the apartment-number attribute could mean that the address does not include an apartment number (not applicable), that an apartment number exists but we do not know what it is (missing), or that we do not know whether or not an apartment number is part of the customer’s address (unknown).
A database for a banking enterprise may include a number of different entity sets. For example, in addition to keeping track of customers and loans, the bank also provides accounts, which are represented by the entity set account with attributes account-number and balance. Also, if the bank has a number of different branches, then we may keep information about all the branches of the bank. Each branch entity set may be described by the attributes branch-name, branch-city, and assets.

Preface 1
1. Introduction
I. Data Models 
Introduction
2. Entity−Relationship Model
3. Relational Model
II. Relational Databases 
Introduction
4. SQL
5. Other Relational Languages
6. Integrity and Security
7. Relational−Database Design
III. Object−Based Databases and XML
Introduction
8. Object−Oriented Databases
9. Object−Relational Databases
10. XML 3
IV. Data Storage and Querying
Introduction
11. Storage and File Structure
12. Indexing and Hashing
13. Query Processing
14. Query Optimization
V. Transaction Management 
Introduction
15. Transactions
16. Concurrency Control
17. Recovery System
VI. Database System Architecture 
Introduction
18. Database System Architecture
19. Distributed Databases
20. Parallel Databases
VII. Other Topics 
Introduction
21. Application Development and Administration
22. Advanced Querying and Information Retrieval
23. Advanced Data Types and New Applications
24. Advanced Transaction Processing