1 / 14

Project

Project. Midterm Review. Midterm Questions Distribution: ~45 % SQL (Joins, Group by, Correlated Subqueries, Subqueries in the “from” clause, Views) ~35 % Modeling, Constraints and Triggers (see next pages) ~10 % HTML DB ~10% General Questions. Midterm Review. Bank Object Model.

erv
Download Presentation

Project

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Project Data Management

  2. Midterm Review • Midterm Questions Distribution: • ~45 % SQL (Joins, Group by, Correlated Subqueries, Subqueries in the “from” clause, Views) • ~35 % Modeling, Constraints and Triggers (see next pages) • ~10 % HTML DB • ~10% General Questions Data Management

  3. Midterm Review Bank Object Model Data Management

  4. Midterm Review Bank Relational Model Data Management

  5. Midterm Review • Bank DDL • drop table loan cascade constraints; • drop table customer cascade constraints; • drop table account cascade constraints; • drop table depositor cascade constraints; • drop table branch cascade constraints; • drop table borrower cascade constraints; • CREATE TABLE account ( • account_number SMALLINT NOT NULL, • balance SMALLINT NOT NULL, • branch_name SMALLINT, • CONSTRAINT PK_T_account14 PRIMARY KEY (account_number) • ); • CREATE TABLE branch ( • branch_name SMALLINT NOT NULL, • branch_city SMALLINT NOT NULL, • assets SMALLINT NOT NULL, • CONSTRAINT PK_T_branch13 PRIMARY KEY (branch_name) • ); Data Management

  6. Bank DDL (continued) • CREATE TABLE customer ( • customer_name SMALLINT NOT NULL, • customer_street SMALLINT NOT NULL, • customer_city SMALLINT NOT NULL, • CONSTRAINT PK_T_customer15 PRIMARY KEY (customer_name) • ); • CREATE TABLE loan ( • loan_number SMALLINT NOT NULL, • amount SMALLINT NOT NULL, • branch_name SMALLINT, • CONSTRAINT PK_T_loan16 PRIMARY KEY (loan_number) • ); • CREATE TABLE depositor ( • customer_name SMALLINT NOT NULL, • account_number SMALLINT NOT NULL, • CONSTRAINT PK_T_517 PRIMARY KEY (customer_name, account_number) • ); • CREATE TABLE borrower ( • loan_number SMALLINT NOT NULL, • customer_name SMALLINT NOT NULL, • CONSTRAINT PK_T_618 PRIMARY KEY (loan_number, customer_name) • ); Midterm Review Data Management

  7. Bank DDL (continued) • CREATE INDEX TC_T_loan534 ON loan (branch_name); • CREATE INDEX TC_T_account532 ON account (branch_name); • CREATE INDEX TC_T_5535 ON depositor (customer_name); • CREATE INDEX TC_T_5536 ON depositor (account_number); • CREATE INDEX TC_T_6537 ON borrower (loan_number); • CREATE INDEX TC_T_6538 ON borrower (customer_name); • ALTER TABLE loan ADD CONSTRAINT FK_T_loan14 FOREIGN KEY (branch_name) • REFERENCES branch (branch_name) ON DELETE SET NULL; • ALTER TABLE account ADD CONSTRAINT FK_T_account13 FOREIGN KEY (branch_name) • REFERENCES branch (branch_name) ON DELETE SET NULL; • ALTER TABLE depositor ADD CONSTRAINT FK_T_515 FOREIGN KEY (customer_name) • REFERENCES customer (customer_name) ON DELETE CASCADE; • ALTER TABLE depositor ADD CONSTRAINT FK_T_516 FOREIGN KEY (account_number) • REFERENCES account (account_number) ON DELETE CASCADE; • ALTER TABLE borrower ADD CONSTRAINT FK_T_617 FOREIGN KEY (loan_number) • REFERENCES loan (loan_number) ON DELETE CASCADE; • ALTER TABLE borrower ADD CONSTRAINT FK_T_618 FOREIGN KEY (customer_name) • REFERENCES customer (customer_name) ON DELETE CASCADE; Midterm Review Data Management

  8. Bank DDL (continued) • INSERT INTO branch VALUES (10, 20, 30); • INSERT INTO account VALUES (100, 200, 10); • create or replace and compile java source named BRANCH_METHODS • as • import java.util.*; • import java.sql.*; • public class branchMethods{ • public static void branch_name_update(int old_branch_name, int new_branch_name) throws SQLException { • Connection conn = DriverManager.getConnection("jdbc:oracle:kprb:@"); • System.out.println("In branch_name_update, updating branch_name in account to " + new_branch_name + " where branch_name is " + old_branch_name); • String query = "update account set branch_name = ? where branch_name = ? "; • PreparedStatement pstatement = conn.prepareStatement(query); • pstatement.setInt(1, new_branch_name); • pstatement.setInt(2, old_branch_name); • pstatement.executeQuery(); } • } • / Midterm Review Data Management

  9. Bank DDL (continued) • CREATE OR REPLACE PROCEDURE "SCOTT"."BRANCH_NAME_UPDATE" ( • old_branch_name number, new_branch_name number) • as language java • name 'branchMethods.branch_name_update(int, int)'; • / • set serveroutput on size 10000 • call dbms_java.set_output(10000); • CREATE OR REPLACE TRIGGER "SCOTT"."BRANCH_NAME_UPDATE" BEFORE • UPDATE OF "BRANCH_NAME" ON "SCOTT"."BRANCH" FOR EACH ROW call BRANCH_NAME_UPDATE(:old.branch_name, :new.branch_name) • / Midterm Review Data Management

  10. Midterm Review Object Model Data Management

  11. Midterm Review Relational Model Data Management

  12. Midterm Review • DDL • CREATE TABLE T_e ( • num SMALLINT NOT NULL, • name SMALLINT, • CONSTRAINT PK_T_e15 PRIMARY KEY (num), • CONSTRAINT TC_T_e142 UNIQUE (name) • ); • CREATE TABLE T_d ( • age SMALLINT NOT NULL, • name SMALLINT NOT NULL, • id SMALLINT NOT NULL, • CONSTRAINT PK_T_d14 PRIMARY KEY (name, id) • ); • CREATE TABLE T_c ( • id SMALLINT NOT NULL, • CONSTRAINT PK_T_c16 PRIMARY KEY (id) • ); • CREATE TABLE T_b ( • name SMALLINT NOT NULL, • id SMALLINT, • CONSTRAINT PK_T_b12 PRIMARY KEY (name) • ); Data Management

  13. Midterm Review • DDL (continued) • CREATE TABLE T_a ( • id SMALLINT NOT NULL, • CONSTRAINT PK_T_a11 PRIMARY KEY (id) • ); • CREATE INDEX TC_T_e141 ON T_e (name); • CREATE INDEX TC_T_d139 ON T_d (id); • CREATE INDEX TC_T_d138 ON T_d (name); • ALTER TABLE T_e ADD CONSTRAINT FK_T_e11 FOREIGN KEY (name) • REFERENCES T_b (name) ON DELETE SET NULL; • ALTER TABLE T_d ADD CONSTRAINT FK_T_d9 FOREIGN KEY (name) • REFERENCES T_b (name) ON DELETE CASCADE; • ALTER TABLE T_d ADD CONSTRAINT FK_T_d10 FOREIGN KEY (id) • REFERENCES T_a (id) ON DELETE CASCADE; • ALTER TABLE T_c ADD CONSTRAINT FK_T_c14 FOREIGN KEY (id) • REFERENCES T_a (id) ON DELETE CASCADE; • ALTER TABLE T_b ADD CONSTRAINT FK_T_b13 FOREIGN KEY (id) • REFERENCES T_c (id) ON DELETE SET NULL; Data Management

  14. Midterm Review create table "DUMMY" ( "ID" NUMBER, constraint "DUMMY_PK" primary key ("ID") ) / comment on table "DUMMY" is 'This table was created using the table wizard.' / create sequence "DUMMY_SEQ" / create trigger "BI_DUMMY" before insert on "DUMMY" for each row begin for c1 in ( select DUMMY_SEQ.nextval next_val from dual ) loop :new.ID := c1.next_val; end loop; end; / Data Management

More Related