Create DB Schema Manually

Last modified by superadmin on 2018-01-12 20:28

Create DB Schema Manually

  1. Initialize database - open console to your Oracle database in one of the following ways:
    1a. Start -> All Programs -> Oracle Database 10g Express Edition -> Run SQL Command Line. Type 
connect system/system;

 (you may need to use a different user name and password - see Oracle XE Installation). 

1a. In SQL Developer, Toad or similar tool connect to the local or remote Oracle database. 

  1. Execute the following database script to create user: 
CREATE USER mctesting IDENTIFIED BY mctesting;
GRANT ALL PRIVILEGES TO mctesting;

User creation is normally done only during the first time the application is run. In all subsequent exercises we shall just use the username/password pair mctesting/mctesting

  1. Create database schema to store entity data from a previous exercise (see UML Diagram): 
CONNECT mctesting/mctesting;

CREATE TABLE mcuser (mcuser_id NUMERIC(10) NOT NULL,
    email VARCHAR2(50) NOT NULL,  
    firstname VARCHAR2(30) NOT NULL,
    lastname VARCHAR2(30) NOT NULL,
    loginid VARCHAR2(20) NOT NULL,
    password VARCHAR2(20) NOT NULL,
    registered TIMESTAMP NOT NULL,
    CONSTRAINT mcuser_pk PRIMARY KEY (mcuser_id));
CREATE SEQUENCE seq_mcuser_id;


CREATE TABLE assignment (assignment_id NUMERIC(10) NOT NULL,
    user_id NUMERIC(10) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    title VARCHAR2(30) NOT NULL,
    CONSTRAINT assignment_pk PRIMARY KEY (assignment_id));
CREATE SEQUENCE seq_assignment_id;
ALTER TABLE assignment ADD CONSTRAINT assignment_user_fk
    FOREIGN KEY (user_id) REFERENCES mcuser ( mcuser_id );

CREATE TABLE question (question_id NUMERIC(10) NOT NULL,
    assignment_id NUMERIC(10) NOT NULL,
    content CLOB,
    CONSTRAINT question_pk PRIMARY KEY(question_id));
CREATE SEQUENCE seq_question_id;
ALTER TABLE question ADD CONSTRAINT question_assignment_fk
    FOREIGN KEY (assignment_id) REFERENCES assignment( assignment_id );


CREATE TABLE alternative (alternative_id NUMERIC(10) NOT NULL,
    question_id NUMERIC(10) NOT NULL,
    content CLOB,
    CONSTRAINT alternative_pk PRIMARY KEY (alternative_id));
CREATE SEQUENCE seq_alternative_id;
ALTER TABLE alternative ADD CONSTRAINT alternative_question_fk
    FOREIGN KEY (question_id) REFERENCES question ( question_id );

 For the entity 'user' a table 'mcuser' (multiple choice user) is created, since 'user' appears to be a reserved word. 

  1. List all the constraints: 
DESCRIBE all_constraints;
SELECT constraint_name, table_name FROM all_constraints WHERE
  owner='MCTESTING' AND (constraint_type = 'P' OR constraint_type = 'R');

 This should display the following: 

CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------
MCUSER_PK                      MCUSER
ASSIGNMENT_PK                  ASSIGNMENT
ASSIGNMENT_USER_FK             ASSIGNMENT
QUESTION_PK                    QUESTION
QUESTION_ASSIGNMENT_FK         QUESTION
ALTERNATIVE_PK                 ALTERNATIVE
ALTERNATIVE_QUESTION_FK        ALTERNATIVE
  1. If for any reason you need to create the database anew, use this clean.sql script: 
DROP TABLE alternative;
DROP TABLE question;
DROP TABLE assignment;
DROP TABLE mcuser;
DROP SEQUENCE seq_alternative_id;
DROP SEQUENCE seq_question_id;
DROP SEQUENCE seq_assignment_id;
DROP SEQUENCE seq_mcuser_id;
Tags:
Created by Kalvis Apsītis on 2008-03-16 18:19
    
This wiki is licensed under a Creative Commons 2.0 license
XWiki Enterprise 6.4 - Documentation