Relational Model, Keys & Integrity Rules

What is a Derived Attribute?

A derived attribute its value is computed from other attributes. It is indicated in ER diagram using a dotted line connecting the attribute with the entity. e.g.: employee age can be calculated from the date of birth and current date.

What is a Composite Key?

A key that is composed of more than one attributes is known as a Composite Key.

What are Relationships Cardinality in an ERD?

Cardinality means "count," and is expressed as a number (Min, Max) Maximum cardinality is the maximum number of entity instances that can participate in a relationship. [1 or M] Minimum cardinality is the minimum number of entity instances that must participate in a relationship. [1 or 0] Established by business rules.

Describe Relationship Participation in ERD?

Optional participation: One entity occurrence does not require corresponding entity occurrence in particular relationship As shown in the below examples Minimum cardinality of zero [0] indicating optional participation is indicated by placing an oval next to the optional entity. Mandatory participation: One entity occurrence requires corresponding entity occurrence in particular relationship As shown in the below examples Minimum cardinality of one [1] indicating mandatory (required) participation and it is not indicated by the ERD Chen Model

What is the most widely used "conceptual model" of Databases?

The Entity Relationship Model • Introduced by Chen in 1976 • Most widely used "conceptual model" of DBs. • Graphical representation of entities, attributes and the relationships among entities in a database structure(depending on the diagram style) varying amounts of other info such as connectivities, cardinalities, keys, weakness, ... • An ER model of an environment forms the basis of an ER diagram (ERD) or several ERDs. • Diagrams based on the/a model are a widely accepted and adopted graphical approach to database design.

What are the differences between Simple and Composite attributes?

• A simple attribute cannot be subdivided: e.g. employee has simple attributes like Salary, Gender, and Department. • A composite attribute can be subdivided to further additional attributes. e.g. :Name, First name, Middle Initials, Last name

What are the differences between Single-Valued and Multivalued Attributes?

• A single-valued attribute can have only a single value. e.g. : a car can have only one car year. • A multivalued attribute can have many values. e.g. : a car may have several body parts colors (top color, body color..etc) • Multivalued attributes are shown in ER diagram by a double line connecting to the entity.

Describe Associative (Composite) Entities in ERD?

• Also known as bridge entities • Used to implement M:N relationships • Composed of primary keys of each of the entities to be connected • May also contain additional attributes that play no role in connective process

Describe 'Multivalued Attributes' and its Advantages and Disadvantages:

• Another possibility: Within original entity type, split the attribute into several different attributes not corresponding to specific components of the entity. Disadvantages: The attribute may in reality need to be split differently for different entities in the entity type (e.g. different cars). The attribute may not have naturally namable aspects at all. E.g., imagine blotches of color in random places on a car • Another possibility: Within original entity type, split the attribute into several different attributes not corresponding to specific components of the entity. E.g., have attributes called Colour1, Colour2, ... , Colour6. -Advantage: copes with the no-identifiable-components problem and the different-split problems. -Disadvantages: Have to set aside enough columns to accommodate the conceivable max, but if this max is large and not often approached then have a lot of wasted space. Searching for a colour, or doing insertions and deletions, can be very cumbersome. • Often Better: Replace the attribute by a new 1:M relationship to a new entity type holding the original attribute's data. If the components of the original attribute are conceptually distinguishable in a natural way, the new entity can have an attribute whose values identify those components.

Describe the phases of Database Design:

• Conceptual design begins with the collection of requirements and results needed from the database. It is high level description (often done with Entity Relationship Diagram (ERD)) • Logical schema is a description of the structure of the database (Relational, Network, etc.) - translate ERD into DBMS data model • Schema Refinement consistency, normalization • Physical schema is a description of the implementation (programs, tables, dictionaries and catalogs)

Define the Entity Relationship Model?

• Entity Relational database model offers a conceptual view of data. • Graphical representations to model database components

Describe the Entities and Attributes Notation in Chen Model:

• Entity represented by rectangle with entity's name • Entity name, a noun, written in capital letters • Attributes represented by ovals connected to entity rectangle with a line • Each oval contains the name of attribute it represents

Describe Relationship Degree in ERD?

• Indicates number of entities or participants associated with a relationship • Unary relationship (degree =1) -Association is maintained within single entity • Binary relationship (degree =2)-Two entities are associated • Ternary relationship (degree =3)-Three entities are associated

Create an ERD that represents a business situation:

• Step 1) Business Rules • Step 2) Listing Entities and Attributes (considering the attribute class) • Step 3) Simple ERDs with relations(considering Connectivities and Cardinalities and Participation) • Step 4) The Complete ERD STEP 1) Identify the Business Rules • A department employs many employees, but each employee is employed by one department. • Some employees, known as "rovers," are not assigned to any department. • A division operates many departments, but each department is operated by one division • An employee may be assigned to many projects and a project may have many employees assigned to it. • A project must have at least one employee assigned to it. • One of the employees manages each department. • One of the employees runs each division. Step 2) Make a list of the Entities and their Attributes •Entity: EMPLOYEE -Attributes (ID, First name, Last name, Salary, title and data of birth) •Entity: DIVISION -Attributes DIVISION ID, business sector name •Entity: DEPARTMENT -Attributes Department ID, Department name and location •Entity: PROJECT -Attributes Project ID, Project name and Project Budget. Step 3) List ALL simple Relations [DIVISION] 1 <operates> M [DEPARTMENT] [EMPLOYEE] 1 <runs> 1 [DIVISION] [EMPLOYEE] 1 <manages> 1 [DEPARTMENT] [EMPLOYEE] N <assigned> M [PROJECT] [DEPARTMENT] 1 <employs> M [EMPLOYEE] Connectivities and Cardinalities and Participation My procedure for determining the cardinality: A DIVISION will operate a minimum of ____1____ DEPARTMENT A DIVISION will operate a maximum of ____N____ DEPARTMENTs Then reverse the order: A DEPARTMENT is operated by a minimum of ___1_____ DIVISIONs A DEPARTMENT is operated by a maximum of ____1____ DIVISIONs

Describe Weak Entities in ERD?

• Weak entity meets two conditions -Existence-dependent, i.e. Entity exists in database only when it is associated with another related entity occurrence -Primary key partially or totally derived from parent entity in relationship • Database designer determines whether an entity is weak based on business rules

Define a Primary Key:

•A primary key for a table (entity type) is a candidate key that the DB designer has chosen as being the main way of uniquely identifying a row (entity). •Primary keys are the main way of identifying target entities in entity relationships, e.g., the way to identify someone's employing organization. •Cannot have null values (A null value is no value, it is NOT equal to a zero or a blank space). •For efficiency (and correctness) reasons, the simpler that primary keys are, the better. •Typical primary keys examples are Identity numbers (of people, companies, products, courses, etc.), or combinations of them with one or two other attributes. •Composite key: Composed of more than one attribute

Give examples of Superkeys & Candidate Keys:

•Candidate key: {STUDENT ID}; {FNAME, LNAME} looks acceptable but we may get people with the same name •{STUDENT ID, FNAME}, {STUDENT ID, LNAME} and {STUDENT ID, FNAME, LNAME} satisfy uniqueness, but are not minimal. •{FNAME} and {LNAME} do not give a unique identifier for each row •{STUDENT ID} will be the best candidate key.

What are business rules and how do they influence database design?

•DB designer gains the main information about the organization which is considered as the main blocks of building a data model. •Business Rules allow designer to: •understand the nature, role, and scope of data •understand business processes •develop appropriate relationship participation rules and constraints •Translating Business Rules into Data Model •Nouns translate into entities •Verbs into relationships among entities •Identify the relationship type and connectivity •The translation step should consider a comprehensive and unique object names.

Keys in a database, describe?

•Each row in a table must be uniquely identifiable by a key •A superkey for a table is a collection of one or more attributes that determines all the other attributes in the table, i.e. determines a whole row. •Trivially, the collection of all the attributes is a superkey. •A set of attributes in a relation is called a candidate key if, and only if, •Every tuple has a unique value for the set of attributes (uniqueness) •No proper subset of the set has the uniqueness property (minimality) •To determine what is a candidate key, use knowledge of the real world - (what is going to stay unique!)

Describe the Relational Model?

•Implemented through the Relational Data Management System (RDBMS) •Relational database model offers a logical view of data. •Hides complexity represented in hierarchal and network models from the user •Entity is mapped to a relational table •Relational table stores collection of related entities •Attributes is mapped to a column table

Explain what Foreign Keys are:

•Remember!! Relationships are represented by associative linking by means of shared attributes •Standardly, a relationship is represented by means of Foreign keys. •Foreign key: an attribute whose values match primary key values in the related table •Referential integrity: a set of attributes in the first (referencing) relation is a Foreign Key if its value always either •matches a Candidate Key value in the second (referenced) relation, or •is NULL