DBA110 Database Concepts

Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 1 of 13

Lab 3 - Data Types and Database Design
3.1. Objectives:
To study various data types in MySQL
To understand database design process in MySQL
3.2. Goals:
Understand data types in MySQL
Implement normalizing tables
3.3. Grading:
Student need to show instructor the answer to normalization after finishing the
command line practicing SQL statements. Save the answers into a file called
normalize-<student-lastname>.txt and submit it through Blackboard.

3.4 Data Types


Each table column value can be one of several different data types, depending on the form of that data. For
example, in the Recipe Instructions table the Instruction column values are all letters (characters), as
opposed to the Num column which are all numbers. These columns are assigned the appropriate data types
for each column when the table is created.

In MySQL (as in most database systems), the data types available can be broken down into four categories:

Numeric Numeric values (Integers, Floating-Point, Fixed-Point and Bit-field)

Character Text strings

Binary Binary data strings

Temporal Time and dates


The ABCs of Data Types:
A -- Apt - the data needs to be represented in the type most fitting the entity it represents
B -- Brief - if you choose the smallest fitting size it saves storage space and helps performance
C -- Complete - the type needs to include the best guess for max size this data set will ever grow

Choosing a data type is primarily a matter of obtaining a model that is closest to your business reality, and thus
most capable of representing your data. Once it is clear how your data should be represented, you can determine
the best mapping between how you want it to be and the capabilities of the database. If you have a clear
understanding of the possible mappings, you should then consider implementation efficiency.

Within each category there are numerous specific data types that use varying amounts of memory and disk
space, which have varying effects on performance. Choosing the best data type for the column has a small
effect on performance in an individual record, but as the database grows these small effects can lead to
larger effects. This should be taken into account early in the design process, before they become
performance issues.


3.4.1 Numeric Data Types


For storing numeric data, MySQL provides integer data types, floating-point types that store approximate-
value (real) numbers, a fixed-point type that stores exact-value (real) numbers, and a BIT type for bit-field
values. When you choose a numeric data type, consider the following factors:

DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 2 of 13

The range of values the data type represents

The amount of storage space that column values require

The column precision and scale for floating-point and fixed-point values

Precision and scale are terms that apply to floating-point and fixed-point values, which can have both an
integer part and a fractional part. Precision is the number of significant digits. Scale is the number of digits
to the right of the decimal point.


Integer Data Types:
The integer data type is used for storing whole numbers. By definition, an integer value does not have a decimal
digits part (such as 23.56). The following is the list of numeric data types and their associated attributes:

Must be a whole number

TINYINT a very small integer data type

SMALLINT a small integer data type

MEDIUMINT a medium sized integer data type

INT, INTEGER - a normal (average) size integer data type

BIGINT - a large integer data type


Integer data types are assigned using the following syntax:

INT [(<M>)]


Note: M indicates the maximum display width for integer types. The maximum legal display width is
255. Display width is unrelated to the range of values a type can contain.

Integer Example:

World database, City table, Population column

Population INT(11)

Largest value output (uses only 8 digits, although 11 are allowed)

10500000


All integer types can have an optional (non-standard) attribute UNSIGNED. Unsigned values can be used
when you want to allow only non-negative numbers in a column and you need a larger upper numeric range
for the column. For example, if an INT column is UNSIGNED, the size of the column's range is the same
but its endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.


When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces
is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is
retrieved as 00004. If you specify ZEROFILL for a numeric column, MySQL automatically adds the
UNSIGNED attribute to the column.


The integer data types are summarized in the following table, which indicates the amount of storage per
value that each type requires as well as its range.




DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 3 of 13


Type
Storage
Required

Signed Range

Unsigned Range
TINYINT 1 byte 128 to 127 0 to 255
SMALLINT 2 bytes 32,768 to 32,767 0 to 65,535
MEDIUMINT 3 bytes 8,388,608 to 8,388,607 0 to 16,777,215
INT 4 bytes 2,147,683,648 to 2,147,483,647 0 to 4,294,967,295

BIGINT

8 bytes
9,223,372,036,854,775,808 to
9,223,372,036,854,775,807
0 to
18,446,744,073,709,551,615


Integer Data Type Comparison














Floating-Point Data Types:

The floating-point data types include FLOAT and DOUBLE. Each of these types may be used to
represent approximate-value numbers that have an integer part, a fractional part, or both. FLOAT and
DOUBLE data types represent values in the native binary floating-point format (IEEE 754) used by the
server host's CPU. This is a very efficient type for storage and computation, but values are subject to
rounding error.
FLOAT a small (single-precision) floating point number that supports signed values, with
unsigned values disallowing negative numbers.
DOUBLE This is a normal (double-precision) floating point number that supports signed
values, with unsigned values disallowing negative numbers.
Floating-Point columns may be declared with a precision and scale to indicate the total number of
digits (from 1 to 255) and the number of decimal places to the right of the decimal point (from 0 to
30).


The FLOAT data type is assigned using the following syntax:
FLOAT(M,D)
where M is the maximum number of decimal digits that can be stored and D is the number of
digits following the decimal point. A single precision floating point integer is accurate to
approximately 7 decimal places. In simplest terms, if M is less than or equal to 25, the storage taken
up is 4 bytes. If M is greater than 25, the storage taken up is 8 bytes. The default value is NULL (if
column can be NULL) or zero(0) if NOT NULL.
The DOUBLE data type is assigned using the following syntax

DOUBLE(M,D)
where M is the maximum number of decimal digits and D is the number of digits following
the decimal point. A double precision floating point integer is accurate to approximately 15
decimal places. The default value is NULL (if column can be NULL) or zero(0) if NOT NULL.

Floating Point Example:

World database, Country table, GNP entity:

GNP FLOAT(10,2)

Largest value output (uses only 7 digits, although 10 are allowed; 2 to right of decimal point)

8510700.00

DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 4 of 13


Floating-Point Type Comparison:

Type Storage Required Signed Range Unsigned Range


FLOAT


4 bytes


-3.402823466E+38 to -1.175494351E-38

0 and
1.175494351E-38 to
3.402823466E+38


DOUBLE


8 bytes

-1.7976931348623157E+308 to
-2.2250738585072014E-308

0 and
2.2250738585072014E-308 to
1.7976931348623157E+308

Note: The floating point assigned width does not affect how the numbers are actually stored, only how the
number will be displayed.



Fixed-Point Data Types:

The fixed-point data type is DECIMAL. It is used to represent exact-value numbers that have an integer
part, a fractional part, or both.

DECIMAL uses a fixed-decimal storage format: All values in a DECIMAL column have the same number
of decimal places and are stored exactly as given when possible. DECIMAL values are not processed quite
as efficiently as FLOAT or DOUBLE values (which use the processor's native binary format), but
DECIMAL values are not subject to rounding error, so they are more accurate. In other words, there is an
accuracy versus speed tradeoff in choosing which type to use. For example, the DECIMAL data type is a
popular choice for financial applications involving currency calculations, because accuracy is most
important.

The DECIMAL data type is assigned using the following syntax

DECIMAL(M,D)

where M is the maximum number of significant digits that can be stored and D is the number of digits
following the decimal point (from 1 to 255). A double precision floating point integer is accurate to
approximately 15 decimal places (from 0 to 30). The default value is NULL (if column can be NULL) or
zero(0) if NOT NULL.


FixedPoint Example:

If you want to represent values such as dollar-and-cents currency figures, you can use a two-
digit scale:

cost DECIMAL(10,2)

Example value output

650.8


BIT Data Types:

The BIT data type represents bit-field values. BIT column specifications take a width indicating the
number of bits per value, from 1 to 64 bits. The following columns store 4 and 20 bits per value,
respectively:

bit_col1 BIT(4)

bit_col2 BIT(20)

For a BIT(n) column, the range of values is 0 to 2
n
1, and the storage requirement is approximately
DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 5 of 13

INT((n+7)/8) bytes per value.

BIT columns can be assigned values using numeric expressions. To write literal bit values in binary
format, the literal-value notation b'val' can be used, where val indicates a value consisting of the binary
digits 0 and
1. For example, b'1111' equals 15 and b'1000000' equals 64.

Rule of thumb for storage size: n=8 takes 1 Byte


3.4.2 Temporal Data Types

Date and time data types are referred to as temporal data types. MySQL provides data types for
storing different kinds of temporal information. In the following descriptions, the terms YYYY, MM, DD,
hh, mm, and ss stand for a year, month, day of month, hour, minute, and second value, respectively.


TIME

This data type can express time of day or a duration, and has a range of '-838:59:59' to
'838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but TIME columns can be assigned
using either strings or numbers. The storage used is 3 bytes.


YEAR

The YEAR data type is assigned using the following syntax


YEAR [(2|4)]

where the year can be in a two-digit or four-digit format. The default is the four-digit format. In four-
digit format, the allowable values are 1901 to 2155, and 0000. In two-digit format, the allowable values
are
70 to 69, representing years from 1970 to 2069. MySQL retrieves YEAR values in YYYY
format.


DATE

The supported range is '1000-01-01' to '9999-12-31' and the storage used is 3 bytes. MySQL
retrieves DATE values in 'YYYY-MM-DD' format, but DATE columns can be assigned using either
strings or numbers. For example, the date January 9, 1998 would look like this:

'1998-01-09'


DATETIME

This data type allows a date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-
12-31 23:59:59' and the storage used is 8 bytes. MySQL retrieves DATETIME values in 'YYYY-MM-DD
HH:MM:SS' format, but DATETIME columns can be assigned using either strings or numbers.


TIMESTAMP

This data type can be used to express datetime values ranging from '1970-01-01 00:00:00' to partway
through the year 2037. The storage used is 4 bytes and returns a string in the format 'YYYY-MM-DD
HH:MM:SS' with a display width fixed at 19 characters. To obtain the value as a number, add +0 to the
timestamp column. TIMESTAMP values are converted from the current time zone to UTC for storage,
DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 6 of 13

and converted back from UTC to the current time zone for retrieval. (This occurs only for the
TIMESTAMP data type, not for other types such as DATETIME.)


A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation.

To control the initialization and update behavior of a TIMESTAMP column, you add either or both of the
DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes to the column
definition when creating the table with CREATE TABLE or changing it with ALTER TABLE.

The DEFAULT CURRENT_TIMESTAMP attribute causes the column to be initialized with the current
timestamp at the time the record is created. The ON UPDATE CURRENT_TIMESTAMP attribute causes
the column to be updated with the current timestamp when the value of another column in the record is
changed from its current value.

Temporal Data Type Summary:
Type Storage Required Range
DATE 3 bytes '1000-01-01' to '9999-12-31'
TIME 3 bytes '-838:59:59' to '838:59:59'
DATETIME 8 bytes '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP 4 bytes '1970-01-01 00:00:00' to mid-year 2037
YEAR 1 byte 1901 to 2155 (for YEAR(4)), 1970 to 2069 (for YEAR(2))

3.4.3 Character String Data Types


A string data type is used for storing character strings. A character string is a sequence of characters.
A character is a position in a particular character set (or alphabet). Strings are such an important and useful
data type that they are implemented in nearly every programming language.

The following table lists the character string data types provided in MySQL.

Category Type *
Text CHAR
(comparison values) VARCHAR
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

Integer ENUM
(comparison values) SET
* See detailed descriptions below.


Character Sets and Collation:
Every character has a particular symbol associated with it, depending on the character set.
The positions in the character set dictate an implicit sorting order (binary) .
mysql supports a number of alternative sorting orders called collations.
Character string operations (like comparison) are also affected by the collation.
o Characters with the same sorting position are effectively equal to one another.
DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 7 of 13



In MySQL, there are some noteworthy issues affecting strings that must be addressed:
Column definitions for many string data types can include a CHARACTER SET attribute to
specify the character set. (CHARSET is a synonym for CHARACTER SET). The COLLATE
attribute specifies a collation for the character set. For example:

CREATE TABLE t (
c1 VARCHAR(20) CHARACTER SET utf8,
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs)

This table definition creates a column named c1 that has a character set of utf8 with the default
collation for that character set, and a column named c2 that has a character set of latin1 and a case-
sensitive collation.


The CHARACTER SET attribute applies to the CHAR and VARCHAR data types, the TEXT data
types, ENUM and SET data types.

Text Category Data Types:

CHAR This data type is for fixed-length (static) strings. Fixed-length means that a string is stored
using a fixed number of bytes. It is always right-padded with spaces to the specified length when stored.
The syntax for assigning a column to the CHAR data type is ...

CHAR (M)

... where the M stands for the maximum number of characters the CHAR value will allow. That means if
a CHAR is given a length of 10, but the text entered is only 3 characters long, 10 characters will always
be stored. If an attempt is made to set the value of a CHAR greater than the allowed maximum of 255,
the CREATE TABLE or ALTER TABLE statement in which this is done fails with an error. Trailing
spaces are removed when CHAR fields are retrieved.


VARCHAR - This data type is for variable-length (dynamic) strings. Variable-length stores the string
value and string length together. Unlike the CHAR data type, the VARCHAR data type can be used to
store strings with trailing spaces. The syntax for assigning a column to the VARCHAR data type is ...

VARCHAR (M)

... where the M stands for the maximum number of characters the VARCHAR value will allow. The
actual maximum length of a VARCHAR in MySQL is determined by the maximum row size and the
character set used. The amount of storage space that a VARCHAR uses depends on the characters set
as well the number of characters in the string. In the event that a VARCHAR is assigned with a max
length of 10, but only 3 characters are entered, then 3 characters would be stored and only 1 byte used
for length. VARCHAR retains the trailing spaces when retrieved.


String CHAR Example:

World database, CountryLanguage table, Language entity:

Language CHAR(30)

Largest value output (uses only 25 characters, although 30 are allowed)

Southern Slavic Languages

DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 8 of 13

TINYTEXT This data type is for variable-length strings. This data type allows a maximum of 255
characters.
TEXT This data type is for variable-length strings. This data type allows a maximum of 65,535
characters.
MEDIUMTEXT - This data type is for variable-length strings. This data type allows a maximum of
16,777,215 characters.

LONGTEXT - This data type is for variable-length strings. This data type allows a maximum of
4,294,967,295 characters. The maximum effective (permitted) length of LONGTEXT columns
depends on the configured maximum packet size in the client/server protocol and available storage.

Text Category Comparison:

For the storage requirement values, M represents the maximum length of a column. L represents the
actual length of a given value, which may be 0 to M.

Type Storage Required Maximum Length
CHAR(M) M characters 255 characters
VARCHAR(M) L characters plus 1 or 2 bytes 65,535 characters (subject to limitations)
TINYTEXT L characters + 1 byte 255 characters
TEXT L characters + 2 bytes 65,535 characters
MEDIUMTEXT L characters + 3 bytes 16,777,215 characters
LONGTEXT L characters + 4 bytes 4,294,967,295 characters


Integer Category Data Types:

ENUM - This data type is an enumerated data type. An ENUM data type definition entails a list of
strings. A value for that particular ENUM data type must be one of the values specified in that list.
An ENUM column can have a maximum of 65,535 distinct values. ENUM values are represented
internally as integers. The syntax for assigning a column to the ENUM data type is ...

ENUM('value1', 'value2' ...)


String ENUM Example:

World database, Country table, Continent entity:

Continent ENUM('Asia', 'Europe', 'North America', 'Africa',
'Oceana', 'Antarctica', 'South America')

The list of allowed values:

| North America |
| Asia |
| Africa |
| Europe |
| South America |
| Oceania |
| Antarctica |

SET - This data type is a set data type (a set can be thought of as any unordered list or collection
of distinct things considered as a whole). Like the ENUM data type, the definition of a SET
data type comprises a list of pre-defined values. The difference with the ENUM data type is
DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 9 of 13

that a value for that particular SET data type can be a list of any of these values. So a single
value of a SET data type is actually a list of values - not a single, atomic value. A SET
column can have a maximum of 64 members. SET values are represented internally as
integers. The syntax for assigning a column to the SET data type is ...

SET('value1', 'value2' ...)

SET Example:

Allergy table, Symptom column:

Symptom SET('sneezing', 'runny nose', 'stuffy head', 'red eyes')


3.4.4 Binary String Data Types

Binary data types fall under the string data type due to their similarities in storage. Binary data is usually
thought of as being a sequence of bytes, rather than a sequence of characters. An important thing to realize
is that the textual representation of a binary string has usually nothing to do with the 'real' meaning of the
binary string. This is an important difference with respect to character strings, which textual representation
usually corresponds exactly with their meaning. Consequently, binary strings do not have an associated
character set or collation. Binary data types are suitable for storing images, sound, and PDF documents.

The following is the list of binary string data types:

BINARY This data type is similar to the CHAR type, but stores binary byte strings rather than non-
binary character strings.
VARBINARY This data type is similar to the VARCHAR (variable-length) type, but stores binary
byte strings rather than non-binary character strings.
TINYBLOB This data type is a BLOB column with a maximum length of 255 bytes.

A blob is a collection of binary data stored as a single entity in a database management system. Blobs
are typically images, audio or other multimedia objects, though sometimes binary code is stored as a
blob.

BLOB This data type is a BLOB column with a maximum length of 65,535 bytes.
MEDIUMBLOB - This data type is a BLOB column with a maximum length of 16,777,215 bytes.
LONGBLOB - This data type is a BLOB column with a maximum length of 4,294,967,295 bytes.

Binary String Type Comparison:

For the storage requirement values, M represents the maximum length of a column. L represents the actual
length of a given value, which may be 0 to M.

Type Storage Required Maximum Length
BINARY(M) M bytes 255 bytes
VARBINARY(M) L bytes plus 1 or 2 bytes 65,535 bytes (subject to limitations)
TINYBLOB L + 1 bytes 255 bytes
BLOB L + 2 bytes 65,535 bytes
MEDIUMBLOB L + 3 bytes 16,777,215 bytes
LONGBLOB L + 4 bytes 4,294,967,295 bytes


3.4.5 Data Type Considerations


Data storage and retrieval is a large issue in the tuning of the MySQL server and ensuring that data integrity
DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 10 of 13

is maintained. When designing the database, the following are some data type considerations that must be
taken into account:
Use Appropriate Data Type An example of this is storing numbers as strings. 09 and 9 are the
same number but different strings and can lead not only to compromised data, but additional processes
to convert the string to a numeric value prior to performing calculations.
Use Appropriate Length Consider the maximum size of the column prior to setting the data type.
For a persons age, a TINYINT unsigned data type (0 to 255) is appropriate. Using an integer unsigned
data type (0 to 4,294,967,295) here would be a waste of storage and affect performance over the lifetime
of the database.
Use Appropriate Character Length For columns that have the potential for holding varying length
of strings, the maximum number of characters and minimization should be taken into account when
selecting a data type. For example, a VARCHAR(64) is an acceptable length for a persons last name
column.

3.5 The Meaning of NULL


NULL is an SQL keyword used to define data types as allowing a missing (or absent) value. It is also a
query result. The concept of NULL can actually have several meanings such as; no value, unknown
value, missing value, out of range, not applicable, none of the above, and so on. However, all the
descriptions of NULL can be split into two categories:

UNKNOWN

There is a value and the entity posses the value, but the precise value is unknown at this time.
For example; secret, figure not available, to be determined, impossible to calculate, and
partially unknown

NOT APPLICABLE

There is a value, but the entity does not currently posses the value.
For example; undefined, moot, irrelevant, none and n/a


3.5.1 When to Use NULL


In the beginning stages of database design, when you are making a list of the data that will be included, it
becomes clear that some data may not be available for all columns. These are the cases to scrutinize and
determine whether undefined values should be allowed. Also, this can be changed for an existing table if a
problem is detected due to the nullness of the column.

For instance, the world database has a Country table that contains a column for life expectancy...

`LifeExpectancy` float(3,1) DEFAULT NULL

...but for the countries that have a population of zero (0), there will be no value for this column. Therefore,
it has been set to DEFAULT NULL to allow undefined values without an error. DEFAULT NULL is
equivalent to NULL.

3.5.2 When to Use NOT NULL

Use NOT NULL if the column will never hold nulls. This will ensure the integrity of the data along with
minimizing the processes that have to be put into place to test for nulls or work with nulls.

3.6 Viewing Database Structure

Now that you have learned the elements of designing an efficient database structure, you should be able to
DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 11 of 13

recognize the use of these techniques in an existing database. This section will cover the steps and syntax
required to view and evaluate a database structure.

3.6.1 Viewing a Database in 5 Steps
From within the mysql client, issue the following statements:

1) SHOW DATABASES

2) USE database_name

3) SHOW TABLES

4) DESCRIBE table_name

5) SELECT * FROM table_name


Lab 3-A

This lab requires you to use the mysql client. You should already have the databases populated
per instruction online at: http://www.unc.edu/~gyzhao/mysql.html#createdb . Otherwise, you
have to finish the steps before proceeding the following:


ACTION (You Do) COMPUTER RESPONSE / Comment
1. First log into your MySQL via the user/password described in previous labs
2. Then, type: (note: no need to type mysql>)

mysql> SHOW DATABASES;
mysql>

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| world |
+--------------------+

3. Type:

mysql> USE world;
Sets world to the default database. Returns
following message:

Database changed
The USE statement specifies the current default
database. This allows you to refer to table
names without explicitly specifying the
database wherein the table resides.
4. Type:
mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
This shows a list of all tables currently
contained in the world database.
DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 12 of 13

5. Type: mysql> DESCRIBE City;

+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra | |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | NULL | |
| CountryCode | char(3) | NO | | NULL | |
| District | char(20) | NO | | NULL | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+

Shows a list of columns describing the structure of the specified table; Field (table column names), Type
(data type), Null (null-ability), Key (indicates if column is a key/index), Default (default value of the
column), and Extra (additional conditions). In this case, the column info for the City table is shown.
6. Type: mysql> SELECT * FROM City;

+------+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+------+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
...
| 4079 | Rafah | PSE | Rafah | 92020 |
+------+----------------+-------------+---------------+------------+
To view the data contained in a table (in tabular form), use the SELECT statement with * (meaning
all columns) FROM the specified table. The result shows the data selected from the City table (for all
4079 rows).

7. Repeat the steps 5 and 6 for the Country and CountryLanguage tables. Note the differences in
structure and content.



Lab 3-B

This lab requires you review the following fictitious Raleigh Movie Library case description as shown in the Excel
worksheet. By using database design you just learned, you need to normalize the table into first normal form
(1NF), second normal form (2NF) and third normal form (3NF) before creating a real movie database in the
future.
Film
No.
Title
Director
No.
Director
Name
Actor
No.
Actor Name Role
Time on
Screen
F1100 Happy Day D101 Jim Alan A1000 Judy Foster Amy Jones 50

A1001 Robert Dinero Harry 30

A1004 Harrison Ford Jack Actress 55

A1009 Eddy Murphy Excel Fuse 65

A1000 Judy Foster Ann Jones 15
F1101 Air Force One D105 Woody Ellen A1000 Judy Foster Amy Jones 50

A1004 Harrison Ford Bill Ford 55

A1009 Eddy Murphy Police one 35

A1002 Mary Jackson Police two 45
DBA110 Database Concepts


Instructors: Chao, Portnoy, Chen, and Sieradzan - Wake Technical Community College Page 13 of 13

F1102 Bride run away D101 Jim Alan A1003 Julia Roberts Amy Jones 50

A1007 David Lynch Sam Strickland 45

A1004 Harrison Ford Robber 35

A1005 John Wayne Cab Driver 25
A1000 Judy Foster News Reporter 65
[Answer]
Step 1 1NF:
Step 2 2NF:
Step 3 3NF: