Information and Data Models

Summary

• Information Models are representations of entities
• Data Models are more specific and include details
• The Relational Model allows for data independence
• Entities are objects like a noun (person, place, or thing), such as a book or an author
• Attributes are the data elements that characterize the entity

ERDs & Types of Relationship

Building block s of a relationship are:

• Entities
• Relationship set
• Crows foot notations

Summary

• The building blocks of a relationship are entities, relationship sets, and crows foot notations.
• In a one-to-one relationship, one entity is associated with one and only one instance of another entity. For example, when one book has only one author.
• In a one-to-many relationship, one entity is associated with one or more instances of another entity. For example, when one book has many authors.
• In a many to many relationship, many instances of an entity are associated with many instances of another entity. For example, when many authors write many different books.

Mapping Entities to Tables

E-R Diagrams

• Foundation for designing a database
• Begin with ERD, and map the ERD to the table
• Example : Entity Book
  • Entity Book has several attiributes
  • Separate the entity from the attributes
• To translate an ERD into a relational database table:
  • The entity becomes the table
  • The attributes become columns in the table

Data Types

1. Character string
   • Fixed length: CHAR(10)
   • Variable length: VARCHAR(20), LONGCHAR
2. Numeric
   • Integer: INT, SMALLINT, BIGINT
   • Decimal: DECIMAL, NUMERIC, FLOAT, SINGLE, DOUBLE
3. Date/time
   • Date: 2021-12-31
   • Time: 02.11.35
   • Timestamp: 2021-12-31-02.11.35.00
4. Boolean: 0 or 1
5. Binary string: a sequence of bytes that represent image, voice, or other media data
6. Large object (LOB): file
7. XML: can store platform agnostic unstructured data in a hierarchical form
8. User defined data types (UDTs): are derived or extended from the built in types

Advantage of using data types

• Data integrity
• Data sorting
• Range selection
• Data calculation
• Use of standard functions

Relational Model Concepts

• First proposed in 1970, based on mathematical model and terms
• Building blocks:
  • Relation
  • Sets
• Set:
  • Unordered collection of distinct elements
  • Items of same type
  • No order and no duplicates

Relation

• Specifies: name of a relation, name and type of each column (attributes)

AUTHOR(Author_ID: char, lastname: varchar, firstname: varchar, 
       email: varchar, city: varchar, country: char)

• Relation Instance: a table made up of rows and columns

  • Column = attributes = field
  • Row = tuple
  • Degree = the number of attributes in a relation
  • Cardinality = the number of tuples

  

  Summary & Highlights

  At this point in the course, you know:

  • The relational model is the most used data model for databases because this model allows for logical data independence, physical data independence, and physical storage independence.
  • Entities are objects that exist independently of any other entities in the database, while attributes are the data elements that characterize the entity.
  • The building blocks of a relationship are entities, relationship sets, and crows foot notations.
  • Relationships can be one-to-one, one-to-many, or many-to-many.
  • When translating an Entity-Relationship Diagram to a relational database table, the entity becomes the table and the attributes become columns in the table.
  • Data types define the type of data that can be stored in a column and can include character strings, numeric values, dates/times, Boolean values and more.
  • The advantages of using the correct data type for a column are data integrity, data sorting, range selection, data calculations, and the of standard functions.
  • In a relational model, a relation is made up of two parts: A relation schema specifying the name of a relation and the attributes and a relation instance, which is a table made up of the attributes, or columns, and the tuples, or rows.
  • Degree refers to the number of attributes, or columns, in a relation.
  • Cardinality refers to the number of tuples, or rows in a relation.

  Practice Quiz: Fundamental Relational Database Concepts

  TOTAL POINTS 5

  Question 1

  Which of the following statements about Data models is correct?

  • A Data model defines the relationships between objects.
  • A Data model describes information at a conceptual level.
  • A Data model is and abstract, formal representation of entities.
  • A Data model is the blueprint of any database system.

  Correct. A data model is the blueprint of any database system.

  Question 2

  Which two types of relationship does Crows foot notation represent ?

  • One-to-many
  • Multiple primary
  • Many-to-many
  • One-to-one

  Correct. Crows foot notation can be used to represent many-to-many relationships, such as many books being written by many authors.

  Question 3

  Entity Relationship Diagrams (ERDs) are the foundation for designing databases. After creating an ERD, what is the first step you must take to map the ERD to the table?

  • Arrange the attributes by importance
  • List the attributes alphabetically
  • Separate the entity from the attributes
  • None of the above

  Correct. Separating the entity from the attributes helps clarify the table (entity) and the columns (attribites).

  Question 4

  Which of the following are advantages of using data types?

  • Auto-correct
  • Data sorting
  • Use of standard functions like AVG(), MIN(), MAX(), and SUM().
  • Range selection

  Correct.

  Question 5

  What are the building blocks of the Relational Model?

  • Mathematical model and terms
  • Index and Elements
  • Collections and Items
  • Relations and sets

  Correct. Building block of Relational Models are: Relations and Sets.

  Graded Quiz: Fundamental Relational Database Concepts

  LATEST SUBMISSION GRADE 100%

  Question 1

  Data can be classified by the level and rigidity of its structure. Which data type is often stored in NoSQL databases?

  • Structured data
  • Unstructured data
  • Regulated data
  • Semi-structured data

  Correct. This data can’t be organized into tabular format in relational databases.

  Question 2

  Which of the following is a type of Information models?

  • Hierarchical
  • Organization
  • Root
  • Tree

  Correct. The most familiar type of Information models is the Hierarchical.

  Question 3

  True or False - In an Entity-Relationship diagram, attributes are connected to multiple entities.

  • True
  • False

  Correct. Attributes are connected to exactly one entity.

  Question 4

  When creating a table from an ERD, the entity becomes the table. What do the attributes become?

  • Cells
  • Rows
  • Columns
  • All of the above

  Correct. Each attribute translates into a column in the table.

  Question 5

  Which data type only holds 1 bit of information; a 0 or a 1?

  • LOB
  • XML
  • Boolean
  • Binary string

  Correct. Only holds 1 bit of information, 0 or 1.