+====================================+ | DATABASE SYSTEMS PROJECT | | Phase III: | | Relational Database Normalization | | & Implementation | +====================================+ When you hand me this phase or later of the project, always attach updated previous phased to it. Phase III: Create Logical and Physical Database with Postgres DBMS. I. Normalization of Your Relations (1) Read the chapter on normalization of relations, and using Wikipedia.com to find the definitions of relational normal forms. More speficically find, read and understand the followings( The definitions on Wikipedia.org are strongly recommended): a. What is the normallization. b. Definitions of the First, Second, Third, and Boyce-Codd Normal Forms. c. Describe the problems or Modification Anomalies if relations are not normalized. (2) Check your relations For each relation: a. Find and list all functionsal dependencie, the funcion dependency set for the relation. b. Use the functional deppedency set, find which normal form a relation is in. Why the relation is that normal form? c. Any modification anomaly exists? d. Based on your database, make a new relation that is on in the first and second normal form, but not in the third or Boyce-Codd NF. List the possible anomalies regarding the relation you have or you created. II. Use PostgresDB's psql to create, load and query your database. The following commands should be practiced and used in this phase: (0). Get familar with Postgres SQL' psql interface commands: ================ Basics of psql Interface ( Postgres SQL) ================== $ psql --u username : to login to psql, and password is needed. username=# : This is psql prompt When you see the prompt above,you have login to Pregres SQL DBMS successfully. You type any SQL commands or any following command for help \help : to list all SQL, psql commands. \copyright : for distribution terms \h : for help with SQL commands \? : for help with psql commands \! os_command : to execute a operating system command. \c user-or-db-name : conect to a different user/dbname. \i filename execute command in filename \q to quit psql-interfae. ------------------------------------------------------------------------------ Some commonly used psql commands: \dt \dv \df : show tables, views, functions, \dp : table-view-sequences. \d tablename : show table attributes, indexes and column and table constraints. \o fileName : save all display or output of psql command into the named file.If no file name followed \o, display will. be one screen. gradebook=# SELECt * FROM students; to list records of all stutents. =============================================================================== (1). CREATE TABLE table_name ...; (2). CREATE VIEW view_name ...; (3). CREATE INDEX idx_name ...; (4). INSERT INTO ... (5). DROP TABLE ... ; (6). DROP VIEW ... ; (7). Purge RECYCLEBIN; (8). COMMIT; (9). ROLLBACK; (10). SELECT (Cartesian, inner, natual, outer join) (11). DELETE (12). UPDATE (13). CREATE or REPLACE FUNCTION ... (14). CREATE or REPLACE PROCEDURE .. (15). CREATE or REPLACE TRIGGER ... (16). DROP PROCEDURE | FUNCTION ... (17). Read DataLoader.java program, and edit your records based on the format that DataLoader required. Compile (javac DataLoader.java) and run the program (java DataLoader) to load the records into tables. ------ The followings are not required on Postgres SQL ------------------- Notice that: The following may be important for generating the major part of your report for this assignment. (1) When you create table and define primary or unique key (not foreign key), Postgres creates indexes for the primary key contraints. To store the primary key and unique key indexes into an designated tablespace, use "USING INDEX TABLESPACE" clause in your create table statement. To create non-unique indexes and stored index into designated table space, use CREATE INDEX command with TABLESPACE clause. (2). You may type command interactively first. If there are some errors, use command EDIT, and save the corrected command in a file using line-mode command "WRITE file_name". (3). Use "SPOOL filename" and SPOOL OUT to save all the queries and the results on both screen and a file. Document your statement by editing the output file. The comments are preceded with two dashes. -------------------------------------------------------------------------- III. Answer all queries listed in phase II. Beside the queries listed in phase II, you need to add serveral queries, each of them will have one or more of the following features: (1) with Aggregate functions. (2) with GROUP_BY clause without HAVING clause, (3) GROUP_BY clause with HAVING clause, (4) with ORDER_BY, (5) Arithmetic operators: +, -, *, is NULL, IS NOT NULL (6) with outer join. (7) Use psql to see if the features such as Title, column formating, break on, set pagesize, save queries results and etc. (8) Outer join, (9) Subquery Use the LIST sqlplus command to display the query in the buffer ( the command you just ran). IV. In your report, Phase 3 Relational Database Normalization and Implementation 3.1. Normalization of Relations 3.1.1 What is normalization? What the 1NF, 2ND, 3NF and BCNF. What are the update anormalies? What is the relationship between Normalizations and update anormalies? Give or make some some relations in your database which are not normalized, and show the possible anomalies that will occur in your data. (From previous exprences, I suggest that don't read previous project since some error or incorrect descriptions used by later classes. Read textbook and use internet search on Third Normal Form, Boyce-Codd Normal Form, Insert/Delete/Update Anomalies. Come out your own description on three anomalies.) 3.1.2 Check and document each of every relations to see if it is in the 3rd or Boyce-Codd normal form. List orginal relation and updated relation that were not in the 3rd or BCNF. 3.2. Describe the main purpose of psql and funcationality provided by psql. 3.3. Describe schema objects allowed in Postgres DBMS; what are they, and the purposes of those object. Also list the schema objects that you have created in your project. Descibe the syntax of statements for creating database schema objects. Group those statements into subsections. 3.4. For each relation, list its relation schema and its contents (relation instance), using DESC and SELECT commands to show the relation schema and the SELECT statement to show the contents of the relation. 3.5. Write queries designed in previous phase in SQL language. Save the SQL statement in a file. Run each of the files to generat the report. The following SQL features will be used in some of your queries (if not, add more new queries to the previious phase): - IS [not] NULL, -[NOT] EXISTS, - GROUP BY and Having, - aggregate functions, - sub-select statement, - create a new table from existing table(s) using CREATE TABLE .... AS SELECT ... - outer join.