IDPLab1 CreateDatabase

-- -- IDPLab1-CreateDatabase.sql

---

-- Creates the database AcctCustDatabase for the Index Lab

-- Begin

CREATE DATABASE AcctCustDatabase;

USE AcctCustDatabase;

CREATE TABLE customer ( customerNumber INTEGER NOT NULL, firstName VARCHAR(50) NOT NULL, lastName VARCHAR(50) NOT NULL );

ALTER TABLE customer ADD CONSTRAINT pk_customer PRIMARY KEY(customerNumber);

CREATE TABLE account ( accountNumber INTEGER NOT NULL, customerNumber INTEGER NOT NULL, accountType INTEGER NOT NULL );

ALTER TABLE account ADD CONSTRAINT pk_account PRIMARY KEY(accountNumber); ALTER TABLE account ADD CONSTRAINT fk_account_customer FOREIGN KEY(customerNumber) REFERENCES customer(customerNumber);

INSERT INTO customer (customerNumber, firstName, lastName)VALUES (1, 'John', 'Doe'); INSERT INTO customer (customerNumber, firstName, lastName)VALUES (2, 'Susan', 'Smith');

INSERT INTO account (accountNumber, customerNumber, accountType) VALUES (100, 1, 1); INSERT INTO account (accountNumber, customerNumber, accountType) VALUES (200, 2, 2);

-- End