Download
1 / 43
451 likes | 830 Views
Database. Basic Definitions. Database: A collection of related data. Database Management System (DBMS): A software package/ system to facilitate the creation and maintenance of a computerized database.
E N D
Basic Definitions • Database: A collection of related data. • Database Management System (DBMS): A software package/ system to facilitate the creation and maintenance of a computerized database. • Database System: The DBMS software together with the data itself. Sometimes, the applications are also included. ( Software + Database )
Data Models • High Level or Conceptual data models provide concepts that are close to the way many users perceive data, entities, attributes and relationships. (Ex. ERD) • Physical data models describes how data is stored in the computer and the access path needed to access and search for data.
Entity Relationship Modeling • Entity-Relationship Diagram (ERD): Identifies information required by the business by displaying the relevant entities and the relationships between them.
Entity Relationship Modeling, (Cont.) • In building a data model a number of questions must be addressed: • What entities need to be described in the model? • What characteristics or attributes of those entities need to be recorded? • Can an attribute or a set of attributes be identified that will uniquely identify one specific occurrence of an entity? • What associations or relationships exist between entities?
Entity • An entity is a thing that exists and is distinguishable -- an object, something in the environment. • Examples : book, item, student • Types of entities: • Regular entity (Strong entity): if its existence does not depend on another entity. • Weak entity: if its existence depends on another entity.
Attribute • An entity has a set of attributes • Attribute defines property of an entity • It is given a name • Attribute has value for each entity and value may change over time • Example : BOOK entity has the following attributes (TITLE, ISBN, AUTHOR, PUBLISHER, YEAR, PRICE)
Attribute Types • Simple:Each entity has a single atomic value for the attribute ; e.g., SSN • Composite: The attribute may be composed of several components ; e.g., Name (FirstName, MiddleName, LastName) • Multi-valued:The attribute may has more than one value for a given entity; e.g., a book may have many authors • Derived attributes: The attributes whose values are generated from other attributes using calculations ; e.g., Age is derived by subtracting current date from birthdate
Key Attribute • Primary Key: an attribute of an entity type for which each entity must have a unique value; e.g., SSN of EMPLOYEE. • Composite key: e.g., ID is a key of the applicant entity type with components (National_ID, Application_no) • Foreign Key (referential attributes):Attributes that define relationships between entities. The attributes of a foreign key in one entity are the attributes of a primary key in another entity; e.g., Department ID is the primary key of Department and Department ID is a foreign key of Employee defining the relationship "Employee works for Department"
Relationships • Relationships: A relationship is a connection between entity classes. • The cardinalityof a relationship indicates the number of instances in entity class E1 that can or must be associated with instances in entity class E2. • One-One Relationship:(citizen – passport , • One-Many Relationship: (student-Advisor, Customer-Order) • Many- Many Relationship: (e.g. Student-Organization, Order-Products) • Recursive Relationships: A relationship in which the same entity participates more than once.
Relationships, (cont.) • Binary relationship: between two entity sets • e.g., binary relationship set STUDY between STUDENT and COURSE • Ternary relationship: among three entity sets • e.g., ternary relationship STUDY could be ternary among STUDENT, COURSE and TEACHER • A relationship may have attributes e.g., attribute GRADE and SEMESTER for STUDY
ERD Notations • Rectangles represent ENTITY CLASSES • Circles represent ATTRIBUTES • Diamonds represent RELATIONSHIPS • Arcs connect entities to relationships. Arcs are also used to connect attributes to entities. Some styles of entity-relationship diagrams use arrows and double arrows to indicate the one and the many in relationships. • Underline - Key attributes of entities are underlined.
Exercise • A company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. • A department may have several locations. • A department may control a number of projects, each of which has a unique name, a unique number, and a single location. • A project must controlled by department. • We store employee’s name, social security number, address, salary, gender and birth date.
Exercise, (cont.) • An employee must be assigned to one department and must work on one or more projects, which are not necessarily controlled by the same department. • We keep track of the number of hours per week that an employee works on each project. • We also keep track of the direct supervisor of each employee. • We want to keep track of the dependents of each employee for insurance purposes. • We keep each dependent’s first name, gender, birth date and relationship to that employee.
ER-to-Relational Mapping • Step 1: Mapping of Regular Entity Types • Step 2: Mapping of Weak Entity Types • Step 3: Mapping of Binary 1:1 Relation Types • Step 4: Mapping of Binary 1:N Relationship Types. • Step 5: Mapping of Binary M:N Relationship Types. • Step 6: Mapping of Multi-valued attributes. • Step 7: Mapping of N-ary Relationship Types. • Step 8: Map recursive Relationship Types
Step 1: Mapping of Regular Entity Types • Create table for each regular (strong) entity type. • Choose one of key attributes to be the primary key.
Step 2: Mapping of Weak Entity Types • Create table for each weak entity. • Add foreign key that correspond to the owner entity type. • Choose the primary key : ( FK + weak entity Partial PK if any).
Step 3: Mapping of Binary 1:1 Relation Types • Merged two tables if both sides are Mandatory. • Add FK into table with the Mandatory relationship to represent optional side. • Create a third table with just the keys from the two tables in addition to the two table if both sides are optional.
Step 4: Mapping of Binary 1:N Relationship Types • If the N-side is mandatory, add FK to N-side table Add any simple attributes of relationship as column to N-side table. • If the N-side is optional, create a third table The of the new table consists of a concatenation of the keys of the related entities. Include any attributes that were in the relationship.
Step 5: Mapping of Binary M:N Relationship Types • Create a new third table • Add FKs to the new table for both parent tables • Add simple attributes of relationship to the new table if any .
Step 6: Mapping of Multi-valued attributes • Create new table for each multi-valued attribute • Table will include two columns: one for multi-valued attribute + FK column.
Step 7: Mapping of N-ary Relationship Types • If n > 2 then : • Create a new third table • Add FKs to the new table for all parent tables • Add simple attributes of relationship to the new table if any .
Step 8: Map recursive Relationship Types • For recursive entities, two types of mapping rules have been developed: • 1:N recursive relationships, re include the primary key of the table with the recursive relationship in the same table, giving the key some other name.
Step 8: Map recursive Relationship Types • For M:N recursive relationships, create a separate table for the relationship
Referential Integrity • Referential Integrity requires that: • The columns of a foreign key must match in type the columns of the primary key in the referenced table. • The values of the foreign key columns in each row of the referencing table must match the values of the corresponding primary key columns for a row in the referenced table.
Referential Integrity, (cont.) • No Action • only primary keys with no matching foreign keys can be deleted/Updated • the change to the referenced (primary key) table is not performed. • Cascade where the foreign key in the case matching is deleted/updated. • For update (the primary key column values have been modified), the corresponding foreign key columns for referencing rows are set to the new values. • For delete (the primary key row is deleted), the referencing rows (foreign key) are deleted
Referential Integrity, (cont.) • SET NULL • All matching foreign keys are set to null, and delete/update then takes place in other relation. • SET Default • all matching foreign keys are set to default value, and delete/update then takes place in other relation.
