Document Preview:

( Q 1) Normalization Considering the following example of Invoice, you will design relational database by following the step suggested. Please, answer each step. Step1: Identifying all attributes and decide primary key of INVOICE relation. Step2: Identifying all (minimal) functional dependencies. Step3: Normalize INVOICE to 3 NF. ( Q 2) Query Optimizer You are managing the following database system: DBMS: Oracle 11g OS: Sun Solaris System architecture: Web-based application using ASP.Net Purpose of Server: HR management Total size of database: 120 MB Total number of table: 12 Total number of index including Primary Key: 18 Daily average number of transactions: 400 Complexity of SQL: simple Update: not frequently As a database administrator, you need to decide query optimizer, i.e., CBO (Cost-based optimizer) or RBO (Rule-based optimizer). Which one are you going to select for your system? Any Why? ( Q 3) Query Execution Plan You are using Oracle 11g DBMS with Cost-Based Optimizer (CBO). In order to perform SQL tuning for a query, you decide to use ‘set autotrace’ option of SQ*Plus as below: SQL> SET AUTOTRACE ON SQL> select d.department_name, sum(e.salary) 2 from employees e, departments d 3 where e.department_id = d.department_id 4 and e.salary >= 3000 5* group by d.department_name Explain about the output (i.e., Execution Plan) including order of execution and statistical data. ( Q 4) Indexing: B+-tree. Consider constructing a B+-tree of order 3 (i.e. p=3 and p leaf = 2) Show the each step and resulting trees for inserting keys 8, 5, 1, 7, 3, 12, 9, 6 in this order. Step 2: After inserting 3 Step 3: After inserting 12 Step 4: After inserting 9 Step 5: After inserting 6 Consider the B+-tree constructed above, i.e., the result of Step 5, and then assign nodeID (1, 2, 3 ….) from top to bottom, and let to right. For example, root node has nodeID=1. When you look up the key “8”, how many nodes do you need to visit?...

  • 8 years ago
design relational database A+ Tutorial use as Guide
NOT RATED

Purchase the answer to view it

blurred-text
  • attachment
    design_relational_database.docx