---

Defining the relations (in a database)

---

• Defining relations: the CREATE TABLE command.
  • Defining relations in a database schema:

    The CREATE TABLE command is used to define tables (= relations) within a database schema. The relation created by the CREATE TABLE command is initially empty.

    ---

    ---

  • Syntax of the CREATE TABLE command:

    CREATE TABLE relation_name ( attr_name1 type1 [attr_constraint1] , attr_name2 type2 [attr_constraint2] , .... attr_namen typen [attr_constraintn] [ , integrity constrains ] );

    Defines the relation named relation_name with attributes:

    attr_name1 attr_name2 ... attr_namen

  • Each attribute has a data type

  ---

  ---

  ---

  ---

• The Data Types in SQL
  • Numeric Types:
    • INTEGER or INT:

      (ordinary) integer

    • SMALLINT

      short integer

    • DECIMAL(i,j) or DEC(i,j) or NUMERIC(i,j):

      fixed point numbers with i decimal digits precision and j digits after the decimal point fixed point numbers are accurate and do NOT have round off errors Example: DEC(8,3) = fixed point numbers of the format: xxxxx.yyy NOTE: You should use DEC(n,2) in financial application (to avoid roundoff errors !)

    • FLOAT or REAL:

      single precision floating point numbers (with roundoff errors)

    • DOUBLE PRECISION

      double precision floating point numbers (still with roundoff errors)

    ---

    ---

  • Character string
    • CHARACTER(n) or CHAR(n)

      fixed length character strings (number of characters in string is (always) n)

    • VARCHAR(n) or CHAR VARYING(n) or CHARACTER VARYING(n)

      variable length character strings (maximum number of characters in string is n) SQL includes the number of characters of the string when it stores a VARCHAR value In total, VARCHAR(n) stores n + 2 bytes, the 2 extra bytes is for the string length

      NOTE: To denote string, use single quotes: 'John'

    ---

    ---

  • Bit string
    • BIT(n)

      fixed length bit string (number of bits in string is (always) n)

    • BIT VARYING(n)

      variable length bit string (maximum number of bits in string is n)

      NOTE: To denote bit string 10101, use: B'10101'

    ---

    ---

  • Boolean
    • BOOLEAN

      boolean data

      NOTE:

      • Due to the NULL value, SQL uses a three value logic to evaluate boolean expressions:

        When comparing two values x and y, if either x or y is NULL , then some logical comparisons evaluate to an UNKNOWN value rather than true or false, e.g.: x > NULL --> unknown

      • Advice: avoid using NULL values if you can....

    ---

    ---

  • DATE
    • DATE

      Calendar date

      NOTE:

      • DATE values are specified as: DATE'YYYY-MM-DD'

      • Must be preceeded by the keyword DATE

    ---

    ---

  • TIME

    • TIME

      Time of day

      NOTE:

      • TIME values are specified as: TIME'HH:MM:SS'

      • Must be preceeded by the keyword TIME

    ---

  • TIMESTAMP (= date + time)

    • TIMESTAMP

      DATA + TIME

      NOTE:

      • TIMESTAMP values are specified as: TIMESTAMP'YYYY-MM-DD HH:MM:SS'

      • Must be preceeded by the keyword TIMESTAMP

      • Need a space between DATE and TIME

  ---

  Note:

  A database system has the liberty not to provide some SQL datatype Read the documentation of the specific DBMS that you are using to see which data types are available Data types in MySQL: click here

  ---

  ---

  ---

  ---

  ---

  ---

• How I created the "companyDB" in MySQL
  • You can find the commands to create the companyDB in this folder on the department's system (login to a lab work station):

    /home/cs377001/companyDB

    ---

    ---

  • The file /home/cs377001/companyDB/create-table contains the commands to create the tables in the companyDB database:

    create database companyDB; use companyDB; create table department (dname char(15) not null, dnumber int not null, mgrssn char(9) not null, mgrstartdate char(10) ); create table employee (fname char(6) not null, minit char(1), lname char(8) not null, ssn char(9) not null, bdate char(10), address char(25), sex char(1), salary decimal(7,2), superssn char(9), dno int default 1 not null); create table dept_loc (dnumber int not null, dlocation char(10) not null ); create table project (pname char(15) not null, pnumber int not null, plocation char(10), dnum int not null); create table works_on (essn char(9) not null, pno int not null, hours decimal(5,1) not null); create table dependent (essn char(9) not null, name char(10) not null, sex char(1), bdate char(10), relationship char(10));

  ---

  ---

  ---

  ---

  ---

  ---

• Demo: creating a table (relation) and inserting data
  • I will do this demo in class:

    cheung-sql use companyDB; show tables;

    ---

    ---

  • Example: CREATE TABLE:

    CREATE TABLE test ( name char(10), addr varchar(10), id int, salary dec(9,2), bdate date );

    ---

  • Example: insert a tuple into the database

    INSERT INTO test values ( 'John', '123 H Lane', 12345, 45000.65, DATE'1970-4-1' );

    ---

  • Example: select tuples from a table (relation)

    SELECT * from test;

  ---

  ---

  ---

  ---

  ---

  ---

• Deleting Tables (Relations)
  • The DROP TABLE command will delete a relation (its defintion and all its content)

  • Example:

    DROP TABLE test;

  ---

  ---

  ---

  ---

• Finding out information about your database and tables in MySQL:
  • These commands are helpful to find out about the structure of the database:

    show databases list all databases in mySQL use DBName Set current DB to DBName show tables List the tables in the current DB SELECT DATABASE() Return the name of the current DB describe TableName Display the structure of table TableName

  ---

  ---

  ---

  ---

• The DBA: DataBase Administrator.
  • The DBA for a Database system is similar to a System Adminstrator for a computer system

  • The job of the DBA is to ensure that the Database System (and the data) is available 100% of the time

  • The main job is mainly backup data and the "transaction log" and to restore the database when the system fails.

  • He is also responsible for:

    Creating databases Creating tables in the database Creating user accounts Give permission to users to access database

  • So typically, only the DBA would execute the CREATE SCHEMA. He would immediate grant the authorities to some database user who now owns the database schema.

  • This database user would then define tables (relations) within the scheme to store his/her information.