1
00:00:00,000 --> 00:00:06,950
(Music)

2
00:00:06,950 --> 00:00:12,450
Hello and welcome to Database Concepts. In this video, we will learn about

3
00:00:12,450 --> 00:00:18,060
different types of models, how we use
models to map data to tables, and define

4
00:00:18,060 --> 00:00:23,400
relationships between tables. At the end
of this lesson, you will be able to

5
00:00:23,400 --> 00:00:28,500
explain the advantage of the relational
model, explain how the entity name and

6
00:00:28,500 --> 00:00:33,750
attributes map to a relational database
table, describe the difference between an

7
00:00:33,750 --> 00:00:39,809
entity and an attribute, identify some
commonly used data types, and describe

8
00:00:39,809 --> 00:00:45,579
the function of primary keys.
The relational model is the most used

9
00:00:45,579 --> 00:00:50,880
data model for databases because this
model allows for data independence.

10
00:00:50,880 --> 00:00:56,680
Data is stored in a simple data structure.
Tables: this provides logical data

11
00:00:56,680 --> 00:01:02,280
independence, physical data independence, and physical storage independence.

12
00:01:02,280 --> 00:01:07,840
An entity relationship data model, or ER
data model, is an alternative to a

13
00:01:07,840 --> 00:01:13,690
relational data model. Using a simplified
library database as an example, this

14
00:01:13,690 --> 00:01:19,570
figure shows an entity relationship
diagram or ERD that represents entities

15
00:01:19,570 --> 00:01:26,800
called tables and their relationships. In
the library example, we have books. A book

16
00:01:26,800 --> 00:01:32,649
can be written by one or many authors.
The library can have one or many copies

17
00:01:32,649 --> 00:01:39,310
of a book. Each copy can be borrowed by
only one borrower at a time. An entity

18
00:01:39,310 --> 00:01:44,069
relationship model proposes thinking of
a database as a collection of entities

19
00:01:44,069 --> 00:01:50,289
rather than being used as a model on its
own. The ER model is used as a tool to

20
00:01:50,289 --> 00:01:56,099
design relational databases. In the ER
model, entities are objects that exist

21
00:01:56,099 --> 00:02:00,880
independently of any other entities in
the database.

22
00:02:00,880 --> 00:02:07,060
The building blocks of an ER diagram are
entities and attributes. An entity can be

23
00:02:07,060 --> 00:02:11,680
a noun: person, place, or thing. In an ER diagram,

24
00:02:11,680 --> 00:02:16,810
an entity is drawn as a rectangle.
Entities have attributes which are the

25
00:02:16,810 --> 00:02:22,750
data elements that characterize the
entity. Attributes tell us more about the

26
00:02:22,750 --> 00:02:29,650
entity. In an ER diagram, attributes are
drawn as ovals. Using a simplified

27
00:02:29,650 --> 00:02:35,110
library as an example, the book is an
example of an entity. Attributes are

28
00:02:35,110 --> 00:02:39,970
certain properties or characteristics of
an entity and tell us more about the

29
00:02:39,970 --> 00:02:45,489
entity. The entity book has attributes
such as book title, the edition of the

30
00:02:45,489 --> 00:02:51,430
book, the year the book was written, etc
Attributes are connected to exactly one

31
00:02:51,430 --> 00:02:57,340
entity. The entity book becomes a table
in the database and the attributes

32
00:02:57,340 --> 00:03:03,910
become the columns in a table. A table is
a combination of rows and columns. While

33
00:03:03,910 --> 00:03:09,610
mapping, the entity becomes the table.
Having said that, the table has not yet

34
00:03:09,610 --> 00:03:15,340
taken the form of rows and columns. The
attributes get translated into columns

35
00:03:15,340 --> 00:03:21,730
in a table providing the actual table
form of rows and columns. Later, we add

36
00:03:21,730 --> 00:03:27,310
some data values to each of the columns,
which completes the table form. Each

37
00:03:27,310 --> 00:03:32,110
attribute stores data values of
different formats, characters, numbers

38
00:03:32,110 --> 00:03:38,260
dates, currency, and many more besides. In the book table example, the title is made

39
00:03:38,260 --> 00:03:44,049
up of characters. As book titles vary in
length, we can set the variable character

40
00:03:44,049 --> 00:03:49,959
data type for the title column: VAR char.
For character columns that do not vary

41
00:03:49,959 --> 00:03:55,299
in length, we use character or char. The
Edition and year columns would be

42
00:03:55,299 --> 00:04:00,610
numeric. The ISBN column would be carved because it contains dashes as well as

43
00:04:00,610 --> 00:04:06,940
numbers and so on. Using the book entity
mapping as an example, we can create the

44
00:04:06,940 --> 00:04:11,440
tables for the remainder of our
simplified library example using entity

45
00:04:11,440 --> 00:04:17,650
names, like author, author list,
borrower, loan, and copy. The entity

46
00:04:17,650 --> 00:04:23,139
attributes will be the columns of the
tables. Each table is assigned a primary

47
00:04:23,139 --> 00:04:28,960
key. The primary key of a relational
table uniquely identifies each tuple or

48
00:04:28,960 --> 00:04:34,479
row in a table, preventing duplication of
data and providing a way of defining

49
00:04:34,479 --> 00:04:40,539
relationships between tables. Tables can
also contain foreign keys which are

50
00:04:40,539 --> 00:04:47,349
primary keys defined in other tables,
creating a link between the tables. Now

51
00:04:47,349 --> 00:04:51,910
you know that the key advantage of the
relational model is logical and physical

52
00:04:51,910 --> 00:04:57,400
data independence and storage
independence. Entities are independent

53
00:04:57,400 --> 00:05:02,349
objects which can have multiple
characteristics called attributes. When

54
00:05:02,349 --> 00:05:07,000
mapping to a relational database,
entities are represented as tables and

55
00:05:07,000 --> 00:05:12,310
attributes map to columns.
Common data types include characters

56
00:05:12,310 --> 00:05:18,940
such as car and VAR char, numbers such as integer and decimal, and timestamps

57
00:05:18,940 --> 00:05:25,570
including date and time. A primary key
uniquely identifies a specific row in a

58
00:05:25,570 --> 00:05:29,340
table and prevents duplication of data.

59
00:05:29,340 --> 00:05:34,000
(Music)