Homework 3 Practical Implementation of A Simple Rational Database Management System

1. Homework 3Practical Implementation of A Simple Rational Database Management System

2. Contents • Introduction • Requirements • Submissions • Demo Examples

3. Introduction

4. Introduction • Front End • Provide a friendly user interface. • Parse SQL statement into an execution plan. • Display result into a table grid. • Query Processor • Translate execution plan into relational algebra operations. • Storage System • Deal with the data storage in files. • Manipulate the internal organization of relations.

5. DBMS Front End Front End Query in SQL： SELECT CUSTOMER. NAME FROM CUSTOMER, INVOICE WHERE REGION = 'N.Y.' AND AMOUNT > 10000 AND CUTOMER.C#=INVOICE.C Language Processor Internal Form : • ((S SP) Optimizer Query Processor Operator : SCAN C using region index, create C SCAN I using amount index, create I SORT C?and I?on C# JOIN C?and I?on C# EXTRACT name field Operator Processor Calls to Access Method： OPEN SCAN on C with region index GET next tuple . . . Access Method Storage System e.g.B-tree; Index; Hashing Calls to file system： GET10th to 25th bytes from block #6 of file #5 File System database

6. Requirements

7. Requirements • Group-work project • 3 persons per group. • Single person is not allowed. • Programming language • No restriction. • Should provide a friendly UI. • Should deal with SQL commands. • MUST follow the syntax of the demo example. • BUT, You can extend the syntax to support other functions.

8. Requirements (cont.) • Should achieve basic functions • Create and delete relations. • Contain multiple attributes in one relation. • Provide basic attribute type such as integer, real, and string. • Support relational algebra such as select, project, and join. • Support predicate with boolean expression such as AND and OR. • View or save results as a relation. • Insert tuples into a relation. • Update and delete tuples from a relation under a specified condition.

9. Requirements (cont.) • More information • http://www.database.cis.nctu.edu.tw/courses/wpyang/2004S-5138/FinalProject2004.htm

10. Bonus Including but not restricted • Query Processor • Query optimization. • Storage System • B-tree index. • Transaction management.

11. Submissions

12. Submissions • Hard copy report • A detail description of system design and implementation. • Including but not restricted • Your own extended syntax of query language. • The system architecture. • The interfaces between components. • Level of participation, such as student A: 30%, student B: 40%, student C: 30% • Your system in a floppy disk. • Source code. • Execution file.

13. Deadline • Demo: • Location: • The schedule will be announced. Please refer to http://www.database.cis.nctu.edu.tw/courses. • Submissions:

14. Grading • Report: 20%. • System demo: 80%

15. Demo Examples

16. Demo Example • Q1: CreateRelation • Create Table T; • Create Table S (char[2] S#, char[10] Sname, int Status, char[10] City); • Create Table P (char[2] P#, char[10] Pname, real Weight); • Create Table SP (char[2] S#, char[2] P#, int Qty); • Q2: Drop Relation • Drop Table T;

17. Demo Example (cont.) • Q3: Insert single tuple • Insert into S (S#, Sname, Status, City) value (“S1”,”Smith”,20,”London”); • Insert into S (S#, Sname, Status, City) value (“S2”,”Jones”,10,”Paris”); • Insert into S (S#, Sname, Status, City) value (“S3”,”Black”,30,”Paris”); • Insert into P (P#, Pname, Weight) value (“P1”,”Nut”,17.20); • Insert into P (P#, Pname, Weight) value (“P2”,”Bolt”,20); • Insert into P (P#, Pname, Weight) value (“P3”,”Screw”,10.50); • Insert into P (P#, Pname, Weight) value (“P4”,”Test”,5.5); • Insert into SP (S#, P#, Qty) value (“S1”,”P1”,300); • Insert into SP (S#, P#, Qty) value (“S1”,”P2”,200); • Insert into SP (S#, P#, Qty) value (“S2”,”P2”,400); • Insert into SP (S#, P#, Qty) value (“S3”,”P3”,100); • Bonus: Insert multiple tuples • Insert into P(P#, Pname, Weight) Select S#, Sname, Status from S where Status > 10;

18. Demo Example (cont.) • Q4: Update • Update P Set Pname=”Nutt”, Weight=12.9 Where Weight < 15 • Q5: Delete • Delete From P Where Weight < 15; • Q6: Select • Select * From S; • Select * From P;

19. Demo Example (cont.) • Q7: Select + Project • Select S#,Sname,City From S; • Q8: Select + Join • Select * From S, SP Where S.S#=SP.S# • Q9: Select + Project + Join (two relations) • Select S.S#, SP.P#, SP.Qty From S, SP Where S.S#=SP.S#;

20. Demo Example (cont.) • Q10a: Select + Project + Join + And (two relations) • Select S.S#, SP.P#, SP.Qty From S, SPWhere S.S#=SP.S# and SP.Qty < 400; • Q10b: Select + Project + Join + And (more than two relations) • Select S.Sname, P.Pname, SP.Qty From S, SP, PWhere S.S#=SP.S# and P.P#=SP.P#;

21. Demo Example (cont.) • Q11a: Select + Project + Join + multiple And (two relations) • Select S.S#, SP.P#, SP.Qty From S, SPWhere S.S#=”S1” and S.S#=SP.S# and SP.Qty>200; • Q11b: Select + Project + Join + multiple And (more than two relations) • Select S.Sname, P.Pname, SP.Qty From S, SP, PWhere S.S#=”S1” and S.S#=SP.S# and P.P#=SP.P#;

22. Demo Example (cont.) • Q12a: Select + Project + Join + And + Or (two relations) • Select S.S#, SP.P#, SP.Qty From S, SPWhere S.S#=SP.S# and (SP.Qty >=400 or SP.Qty <=200); • Q12b: Select + Project + Join + And + Or (more than two relations) • Select S.Sname, P.Pname, SP.Qty From S, SP, PWhere S.S#=SP.S# and P.P#=SP.P# and (S.S#=”S1” or S.S#=”S2”);

23. Demo Example (cont.) • Q13a: Transaction Abort • Transaction Begin;Insert into S (S#, Sname, Status, City) value (“S4”,”Test”,100,”Taipei”);Transaction Abort; • Select * from S; •  Q13b: Transaction Commit • Transaction Begin;Insert into S (S#, Sname, Status, City) value (“S4”,”Test”,100,”Taipei”);Transaction Commit; • Quit the DBMS process • Select * from S;

24. Demo Example (cont.) • Q14a: Create Index • Create Index SI ON S (Status ASC); • Select * from S; • Q14b: Drop Index • Drop Index SI ; • Select * from S; • Q15: Save the query result

25. Grading Specification