(Music)
Hello and welcome to Database Concepts. In this video, we will learn about
different types of models, how we use models to map data to tables, and define
relationships between tables. At the end of this lesson, you will be able to
explain the advantage of the relational model, explain how the entity name and
attributes map to a relational database table, describe the difference between an
entity and an attribute, identify some commonly used data types, and describe
the function of primary keys. The relational model is the most used
data model for databases because this model allows for data independence.
Data is stored in a simple data structure. Tables: this provides logical data
independence, physical data independence, and physical storage independence.
An entity relationship data model, or ER data model, is an alternative to a
relational data model. Using a simplified library database as an example, this
figure shows an entity relationship diagram or ERD that represents entities
called tables and their relationships. In the library example, we have books. A book
can be written by one or many authors. The library can have one or many copies
of a book. Each copy can be borrowed by only one borrower at a time. An entity
relationship model proposes thinking of a database as a collection of entities
rather than being used as a model on its own. The ER model is used as a tool to
design relational databases. In the ER model, entities are objects that exist
independently of any other entities in the database.
The building blocks of an ER diagram are entities and attributes. An entity can be
a noun: person, place, or thing. In an ER diagram,
an entity is drawn as a rectangle. Entities have attributes which are the
data elements that characterize the entity. Attributes tell us more about the
entity. In an ER diagram, attributes are drawn as ovals. Using a simplified
library as an example, the book is an example of an entity. Attributes are
certain properties or characteristics of an entity and tell us more about the
entity. The entity book has attributes such as book title, the edition of the
book, the year the book was written, etc Attributes are connected to exactly one
entity. The entity book becomes a table in the database and the attributes
become the columns in a table. A table is a combination of rows and columns. While
mapping, the entity becomes the table. Having said that, the table has not yet
taken the form of rows and columns. The attributes get translated into columns
in a table providing the actual table form of rows and columns. Later, we add
some data values to each of the columns, which completes the table form. Each
attribute stores data values of different formats, characters, numbers
dates, currency, and many more besides. In the book table example, the title is made
up of characters. As book titles vary in length, we can set the variable character
data type for the title column: VAR char. For character columns that do not vary
in length, we use character or char. The Edition and year columns would be
numeric. The ISBN column would be carved because it contains dashes as well as
numbers and so on. Using the book entity mapping as an example, we can create the
tables for the remainder of our simplified library example using entity
names, like author, author list, borrower, loan, and copy. The entity
attributes will be the columns of the tables. Each table is assigned a primary
key. The primary key of a relational table uniquely identifies each tuple or
row in a table, preventing duplication of data and providing a way of defining
relationships between tables. Tables can also contain foreign keys which are
primary keys defined in other tables, creating a link between the tables. Now
you know that the key advantage of the relational model is logical and physical
data independence and storage independence. Entities are independent
objects which can have multiple characteristics called attributes. When
mapping to a relational database, entities are represented as tables and
attributes map to columns. Common data types include characters
such as car and VAR char, numbers such as integer and decimal, and timestamps
including date and time. A primary key uniquely identifies a specific row in a
table and prevents duplication of data.
(Music)