1 / 10

Example: Banking Database

6. loan. 3. depositor. 5. account. 存款帳. 4. borrower. 存款戶. 貸款帳. 貸款戶. 國立東華大學試題 : 資料庫管理 資訊管理學系 2011.05.03. Example: Banking Database. 1. branch. 2. customer. 客戶(存款戶,貸款戶). 分公司. Question 1: Write SQL and Output the Results (10%).

lhague
Download Presentation

Example: Banking Database

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. 6. loan 3. depositor 5. account 存款帳 4. borrower 存款戶 貸款帳 貸款戶 國立東華大學試題: 資料庫管理 資訊管理學系2011.05.03 Example:Banking Database 1. branch 2. customer 客戶(存款戶,貸款戶) 分公司

  2. Question 1: Write SQL and Output the Results (10%) • Using the Example -- Banking Database, answering the queries: • Find the loan number for each loan of an amount greater than $1200 • Find the names of all branches in the loan relations for each loan of an amount greater than $1200, and remove duplicates. • How many tuples are selected from the following statement? select  fromborrower, loan • What is the output of the following statement? select branch-name, avg (balance)from accountgroup by branch-namehaving avg (balance) > 1200 2011 Midterm Test-2

  3. Question 2: Database System vs. File System (10%) • In the early days, database applications were built on top of file systems • Drawbacks of using file systems to store data are: • Data redundancy and inconsistency, why? • Difficulty in accessing data, why? • Integrity problems, why? • Application program unstable, why? • Database systems offer solutions to all the above problems, why?

  4. Question 3: Answer the following Terms(20%) • Goal of a DBMS. • Functions of a DBMS • Data Dictionary in DBMS • Schemas and Instances • Superkey • Candidate key • Three levels of an architecture for a database system • Relational-Algebra Operation: Cartesian product • Relational-Algebra Operation: Natural Join • Aggregate Functions

  5. a) Write account • D1 = { } • D2 = { } • D3 = { } • D1 x D2 x D3= account = b) Write c) Why the Relation r is a subset of D1 x D2 x … x Dn , please explain it by using above example. Question 4: Definition of Relation (10%) • Relation: • Formally, given sets D1, D2, …. Dn • D1 x D2 x … x Dn = {(a1, a2, …, an) | where each ai Di} • a Relation r is a subset of D1 x D2 x … x Dn • Thus a relation is a set of n-tuples (a1, a2, …, an) where each ai Di

  6. Assume we have two queries: Q1 and Q2 as follows: Question 5: View (10%) • Q1. create view big-customer as (select account-number, branch-name from account where balance > 500 • Q2 select * from big-customer a) Draw the result of Q2 b) If we want to add a new tuple to big-customerinsert into big-customer values (‘A-999’, ‘Hualien’)Where the insertion values will be placed? Draw a table to show your answer. c) What are the advantages of the View?

  7. 3. account 4. depositor Question 6: Using the following Example to explain theReferential Integrity Constraint (10%) create table account(account-number char(10),branch-name char(15),balance integer, primary key (account-number), create table depositor(customer-name char(20),account-number char(10), primary key (customer-name, account-number), foreign key(account-number) references account, • 存款帳 • references • 存款戶 2011Midterm Test-7

  8. Question 7:Fill in the following components to the Overall System Structure (10%) • Query Processor • Storage Manager • Database administrator • Application programmers • DDL interpreter • Compiler and Linker • Buffer manager • Data dictionary • Index • Data • Naïve users • Application programs low-level data stored database

  9. Question 8: Storage Management (10%) • Storage Manager • is a program module • that provides the interface between the low-level data stored and the application programs and queries submitted to the system. • What are the tasks of the Storage Manager: • Explain the following components of Storage manager: • Authorization and Integrity Manager • Transaction Manager • File Manager • Buffer manager

  10. bank … Question 9: multiple relations vs. Single relation (10%) • Database: A database consists of multiple relations • Information about an enterprise is broken up into parts, with each relation storing one part of the information E.g.: account: stores information about accountsdepositor: stores information about which customer owns which account customer: stores information about customers • If we store all information as a single relation such as bank(account-number, balance, customer-name, ..) Question: The table bank may contain a lot of repetition of information and null values, why?

More Related