Please enable JavaScript.

Coggle requires JavaScript to display documents.

2.0 Relational Data Model, Define the relational databases and…

- - - - Definition:
        A primary key is a special relational database table column (or combination of columns) designated to uniquely identify each table record.
        A primary key is used as a unique identifier to quickly parse data within the table. A table cannot have more than one primary key
        
        Example:
        a special ID attribute either ID or First,Last
    - - Definition:
        A set of attributes which uniquely identify the tuples ( Recorded Data ) in relation or table :
        
        Example:
        { ID, First name, Last Name }
        
        Properties:
        
        It must contain unique values
        
        Must not contain null values
        
        It should contain minimum fields to ensure uniqueness
    - - Definition:
        -An attribute whose values match primary key values in the related table.
        -foreign keys are used to link data in two relations
        
        EXAMPLE: repeat the ID for the next scedule.
  - - - A real-world property of an entity type
      - Represented by an oval or ellipse in E-R diagram
      - Types Of attribute
        
        Simple Attribute & Composite Attribute
        
        A simple attribute contains an atomic value which cannot be further divided. It is simply represented by an oval. Example : Roll_no of Student
        
        A Composite attribute can be further divided into other attributes. We represent this in E-R diagram by connecting an oval with oval i.e the composite attribute is also represented by oval and the further divided attribute all also represented by ovals.
        
        For example, Name of a student can be further divided into first name, middle name and last name.
        
        Single Valued Attribute & Multi-valued Attribute
        
        Singlevalued attribute has only one value and it is represented by a simple oval. Meanwhile, Multivalued Attribute has more than one value. It is represented by a double oval.
        
        Example : Section of a Student (Singlevalued Attribute).
        A student can have more than one e-mail address (Multivalued Attribute).
        
        Stored Attribute & Derived Attribute
        
        The value of Store attribute should be provided by the user. This attribute is stored and can be used for finding the value of other attributes. For example : Date of Birth(DOB) of Student.
        
        The value of Derived attribute is derived from some other attributes. Example : The value of age attribute is derived from the DOB(date of birth) attribute.
        
        Key Attribute & Non-key Attribute
        
        Key attribute is used to uniquely identify the entities of an entity type while the other attributes other than the key attribute are the non-key attributes.
        
        Example: We have Roll_no as the key attribute of the ‘Student’ because two students can never has same roll number (Key Attribute).
        Class, Section, Age, Name etc, are the non-key attributes (Non-key attribute).
    - - A unique identifier (UID) is any identifier which is guaranteed to be unique among all identifiers used for those objects and for a specific purpose. The concept was formalized early in the development of Computer science and Information systems, in general it was associated with atomic data type.
        
        In relational databases, certain attributes of an entity that serve as unique identifiers are called primary keys.
        
        In a database or spreadsheet, unique identifiers may be designated as a specific column or field to help make sorting and filtering through information easier. This also helps trace information back to a specific user or entity within the system.
    - - The degree of a relation is the number of entity types that participate(associate). By seeing an E-R diagram, we can simply tell the degree of a relation i.e the number of an entity type that is connected to a relation is the degree of that relation
        
        For example If we have two entity type ‘Customer’ and ‘Account’ and they are linked using the primary key and foreign key. We can say that the degree of relationship is 2 because here two entities are taking part in the relation
      - Based on the number of entity types that are connected we have the following degree of relation
        
        Binary
        
        N-ary
        
        Ternary
        
        Unary
    - - A single entry in a table is called a Tuple or Record or Row. A tuple in a table represents a set of related data. For example, the above Employee table has 4 tuples/records/rows.
        
        An attribute value is an attribute name paired with an element of that attribute's domain, and a tuple is a set of attribute values in which no two distinct elements have the same name. Thus, in some accounts, a tuple is described as a function, mapping names to values
    - - Domain restricts the values of attributes in the relation and is a constraint of the relational model. However, there are real-world semantics for data that cannot be specified if used only with domain constraints. We need more specific ways to state what data values are or are not allowed and which format is suitable for an attribute.
        
        Example:
        The Employee ID (EID) must be unique or the employee Birthdate is in the range [Jan 1, 1950, Jan 1, 2000]. Such information is provided in logical statements called integrity constraints.
    - - Cardinality” in database design has to do with counting tables and values. With that said, cardinality has three main definitions. It can relate to counting the number of elements in a set, identifying the relationships between tables, or describing how database tables contain a number of values, and what those tables look like in general. Cardinality between tables can be one-to-one, many-to-one or many-to-many.
  - - - EXAMPLE OF RELATIONAL TABLE:
  - - - ✨Rules states that any value( on the relation of the any side).✨Must match a primary key value in the relation of the one side( or the foreign key can be null).
- - - - The values of the set of attributes uniquely identify a tuple
        Candidate keys are: (ID) and (FIRST, LAST)
        Not unique: (First) and (Last)
        The candidate keys are not selected as the primary key are called alternate keys.
      - Primary Key- Candidate key selected to identify tuples (record) uniquely within relation
        
        -Often a special ID attribute is used as the primary key. Either (ID) or (First, Last) could be used as the primary key.
      - NULLs & Primary Keys- Missing information can be represented using NULL and NULL indicates an unknown or missing value. The primary keys however cannot contain NULL values. (entity integrity)
        
        Foreign Key - An attribute whose values match primary key values in the related table. Foreign Keys are used to link data in two relations.
  - - - For example:
        
        :check: The domain of Marital Status has a set of possibilities: Married, Single, Divorced.
        
        :check: The domain of Shift has the set of all possible days: {Mon, Tue, Wed…}.
        
        :check: The domain of First Name is the set of character strings that represents names of people.
      - To facilitate understanding : a domain is a set of acceptable values that a column is allowed to contain. This is based on various properties and the data type for the column