Building Database Tables - brianhigh/data-workshop GitHub Wiki

  1. Defining data types and constraints
  2. Creating a Schema given an ERD
  3. Database design tools (Examples: MySQL Workbench, pgAdmin III)
  4. Example: Create some tables (and relations) from ERD
  5. HoE: Creating more tables from ERD
  6. Discussion
  7. Action Items (readings, videos and tasks)

Action Items (videos, readings, and tasks)

watching
readings
tasks
  • Normalize your Logical Data Model (ERD) in MySQL WB
  • "Forward Engineer" your ERD to create a schema file and tables
  • Post your schema and EER diagram(s) in your project wiki
  • Refer to the example in the "Data Management" project wiki

See also:

Example 1: Creating a PostgreSQL table with a script

We will create a new private schema (and destroy any old one with the same name) and create a new table roster in it. Then we will add some records to the table. Here is the SQL script which does all of this, saved as the file roster.sql, and assuming my own user-id is myuser. In this case, we run the script from a Bash shell.

-- Schema: "private"

DROP SCHEMA IF EXISTS private CASCADE;

CREATE SCHEMA private
  AUTHORIZATION myuser;


-- Table: roster

DROP TABLE IF EXISTS private.roster;

CREATE TABLE private.roster
(
  id serial PRIMARY KEY,
  lname text,
  fname text,
  status text,
  email text
)
WITH (OIDS=FALSE);

\d private.roster

ALTER TABLE private.roster OWNER TO myuser;

INSERT INTO private.roster (lname, fname, status, email) VALUES (
   'Sanders', 'John', 'staff', '[email protected]');
INSERT INTO private.roster (lname, fname, status, email) VALUES (
   'Smith', 'Andy', 'student', '[email protected]');
INSERT INTO private.roster (lname, fname, status, email) VALUES (
   'Jones', 'Sally', 'student', '[email protected]');
INSERT INTO private.roster (lname, fname, status, email) VALUES (
   'Adams', 'Henry', 'faculty', '[email protected]');

SELECT * FROM private.roster;

And here is how we would run the script:

psql --quiet -f roster.sql

This is the output we would expect to see:

psql:roster.sql:3: NOTICE:  drop cascades to table private.roster
psql:roster.sql:11: NOTICE:  table "roster" does not exist, skipping
psql:roster.sql:21: NOTICE:  CREATE TABLE will create implicit sequence "roster_id_seq" for serial column "roster.id"
psql:roster.sql:21: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "roster_pkey" for table "roster"
                             Table "private.roster"
 Column |  Type   |                          Modifiers                          
--------+---------+-------------------------------------------------------------
 id     | integer | not null default nextval('private.roster_id_seq'::regclass)
 lname  | text    | 
 fname  | text    | 
 status | text    | 
 email  | text    | 
Indexes:
    "roster_pkey" PRIMARY KEY, btree (id)

 id |  lname  | fname | status  |        email         
----+---------+-------+---------+----------------------
  1 | Sanders | John  | staff   | [email protected]
  2 | Smith   | Andy  | student | [email protected]
  3 | Jones   | Sally | student | [email protected]
  4 | Adams   | Henry | faculty | [email protected]
(4 rows)
⚠️ **GitHub.com Fallback** ⚠️