- Previously,
we have designed
a Relational data Model for
the Company Database:
- Fact:
- The Relational Data Model has enough details to allow us to define files to store the data in the model !!!
Looking ahead:
- We will learn the SQL (Structure Query Language) that can be used to define the file structure to store the relations in the Company Database
Example:
- The following
SQL command can be used to
define the
file structure for
the Employee
relation:
create table employee (fname char(6), minit char(1), lname char(8), ssn char(9), bdate char(10), address char(25), sex char(1), salary decimal(7,2), superssn char(9), dno int);This will create the following meta data in the MySQL database:
+--------------+------------+-------------+-----------+--------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE | CHAR_MAX_LEN | +--------------+------------+-------------+-----------+--------------+ | companyDB | employee | fname | char | 6 | | companyDB | employee | minit | char | 1 | | companyDB | employee | lname | char | 8 | | companyDB | employee | ssn | char | 9 | | companyDB | employee | bdate | char | 10 | | companyDB | employee | address | char | 25 | | companyDB | employee | sex | char | 1 | | companyDB | employee | salary | decimal | NULL | | companyDB | employee | superssn | char | 9 | | companyDB | employee | dno | int | NULL | +--------------+------------+-------------+-----------+--------------+
This information is obtained using this command in mySQL:
login: cs377001 cheung-sql mysql>> use information_schema; mysql>> select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from columns where TABLE_SCHEMA = 'companyDB' and TABLE_NAME = 'employee';
(I have already created the relations of the company database in MySQL for demo purpose)