Databases Reference
In-Depth Information
How to do it...
The following steps will demonstrate schema denormalization:
1. Launch SQL*Plus and create the schema corresponding to the logical schema shown
in the earlier diagram:
CONNECT sh@TESTDB/sh
CREATE TABLE FRIEND (
ID NUMBER NOT NULL PRIMARY KEY,
FIRSTNAME VARCHAR2(30) NOT NULL,
LASTNAME VARCHAR2(30) NOT NULL,
GENDER CHAR);
CREATE TABLE PHONEKIND (
ID NUMBER NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR2(20) NOT NULL);
CREATE TABLE AVAILABILITY (
ID NUMBER NOT NULL PRIMARY KEY,
WHENAVAILABLE VARCHAR2(30) NOT NULL);
CREATE TABLE PHONE (
ID NUMBER NOT NULL PRIMARY KEY,
PHONEKINDID NUMBER NOT NULL REFERENCES PHONEKIND(ID),
AVAILABILITYID NUMBER REFERENCES AVAILABILITY(ID),
PHONENUMBER VARCHAR2(20) NOT NULL);
CREATE TABLE FRIEND_PHONE (
FRIENDID NUMBER NOT NULL REFERENCES FRIEND(ID),
PHONEID NUMBER NOT NULL REFERENCES PHONE(ID),
CONSTRAINT PK_FRIEND_PHONE PRIMARY KEY (FRIENDID, PHONEID));
2. Populate the schema with some data, for example, the following values:
INSERT /*+APPEND */ INTO FRIEND (ID, FIRSTNAME, LASTNAME, GENDER)
SELECT CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER
FROM SH.CUSTOMERS;
INSERT INTO PHONEKIND(ID, DESCRIPTION) VALUES (0, 'HOME');
