(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)
