1. Homepage
  2. Programming
  3. DTS207TC Database Development and Design - Assessment 001: Individual Coursework: Triggers, and Indexing

DTS207TC Database Development and Design - Assessment 001: Individual Coursework: Triggers, and Indexing

Contact Us On WeChat
DTS207TCDatabase Development and DesignTriggers and IndexingTransaction ManagementQuerying XML DataObject-Relational DatabaseData Warehousing and OLAPSQLPythonXJTLU

  CourseNana.COM

Database Development and Design (DTS207TC) CourseNana.COM

Assessment 001: Individual Coursework CourseNana.COM

Overview CourseNana.COM

This assignment aims to gain experience in understanding the internal functionality of different database management systems, including RDBMS, XML, Object-relational databases, and gain experience in designing data warehouses to perform OLAP operations. The course work will be assessed for the following learning outcomes: CourseNana.COM

A.    Identify and apply the principles underpinning transaction management within DBMS. CourseNana.COM

B.    Demonstrate an understanding of advanced SQL topics. CourseNana.COM

C.    Illustrate the issues related to Web technologies and DBMS and XML as a semi-structured data representation formalism. CourseNana.COM

D.    Identify the principles underlying object-relational models. CourseNana.COM

E.    State the main concepts in data warehousing and data mining. CourseNana.COM

 Assessment Tasks CourseNana.COM

Your task is to answer every question by carefully reading the questions and guidelines for the system setup. Record your thoughts and assumptions, where necessary, while reporting your answers. There are the following five parts for this course work corresponding to five learning outcomes of this module: CourseNana.COM

1.    Advanced SQL, Triggers, Indexing, and Query Optimization CourseNana.COM

2.    Transaction Management CourseNana.COM

3.    Querying XML Data CourseNana.COM

4.    Object-Relational Database CourseNana.COM

5.    Data Warehousing and OLAP CourseNana.COM

  CourseNana.COM

1: Triggers, and Indexing CourseNana.COM

(20 Marks) CourseNana.COM

Q1: Consider the COMPANY database provided along with this assignment. Using the syntax of Oracle triggers, write rules to do the following: CourseNana.COM

Q1(a): Whenever an employee’s project assignments are changed, check if the total hours per week spent on the employee’s projects are less than 30 or greater than 40; if so, notify the employee’s direct supervisor. (5 marks) CourseNana.COM

Q1(b): Whenever an employee is deleted, delete the PROJECT tuples and DEPENDENT tuples related to that employee, and if the employee manages a department or supervises employees, set the Mgr_ssn for that department to NULL and set the Super_ssn for those employees to NULL. (5 marks) CourseNana.COM

You are required to provide the trigger code, SQL statements to test the triggers and screenshots of the database state of affected rows before and after executing trigger test statements and an explanation of your logic. CourseNana.COM

Q1(c)    In the next task, you will be working on the same database to work on indexes by considering the following scenario: (10 marks) CourseNana.COM

Create a tree-based index (one at a time) on the following attribute pairs (Note: by default, indexes are tree-based). CourseNana.COM

Employee.SSN, Department.dnumber CourseNana.COM

Employee.SSN, Department.dname CourseNana.COM

Employee.SSN, Employee.salary CourseNana.COM

Execute the following query and estimate the operation cost for each combination of indexes. For each pair, discuss why or why not that index pair is the best choice based on the cost estimation. CourseNana.COM

SELECT* CourseNana.COM

FROM employee, department CourseNana.COM

Where employee.dno = department.dnumber AND employee.salary > 15000 AND department.dname < 'Research'; CourseNana.COM

  CourseNana.COM

2: Transaction Management (20 Marks) CourseNana.COM

Q2(a): Consider a relation R(A) containing {(4),(5)} and two transactions: CourseNana.COM

T1: Update R set A = A+2; CourseNana.COM

T2: Update R set A = 4*A. CourseNana.COM

Suppose both transactions are submitted under the isolation and atomicity properties. Provide all possible final states of R (values of R) by considering all possible serializable and non-serializable schedules. Provide an explanation for each value it returns. (5 marks) CourseNana.COM

Q2(b)   Consider a table R(A) containing {(3),(4)} and following two transactions run concurrently: CourseNana.COM

T1: Update R set A = 3*A; commit; CourseNana.COM

T2Select avg(A) from R; commit; CourseNana.COM

If transaction T2 executes using READ UNCOMMITTED, what are the possible average values of T2 returns? Provide an explanation for each value it returns. (5 marks) CourseNana.COM

Q2(c)    Consider table R(A) containing {(6),(7)}. Suppose we have the following two transactions running concurrently: CourseNana.COM

 T1: Update T set A=3*A;  CourseNana.COM

insert into R values (6); CourseNana.COM

Commit; CourseNana.COM

T2: Select avg(A) from R; CourseNana.COM

Select avg(A) from R ; CourseNana.COM

Commit; CourseNana.COM

If transaction T2 executes using REPEATABLE READ, what are the possible values returned by T2 in its SECOND select statement?  Provide an explanation for each value it returns. (5 marks) CourseNana.COM

Q2(d)   For each of the following schedules of read, write, commit and abort actions done by transactions T1, T2, T3, state whether they are recoverable or not. If not recoverable, what type of inconsistencies might we have in the final database state? If recoverable, which of the other transactions need to be rolled back?  Note ri (A) and wi (A) mean that transaction Ti (i=1,2,3) reads and writes database object A, respectively. (5 marks) CourseNana.COM

S1 CourseNana.COM

T1 CourseNana.COM

T2 CourseNana.COM

T3 CourseNana.COM

w1(A); CourseNana.COM

  CourseNana.COM

  CourseNana.COM

w1(B); CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

abort; CourseNana.COM

  CourseNana.COM

r2(A); CourseNana.COM

w2(A*4) CourseNana.COM

  CourseNana.COM

r2(B); CourseNana.COM

  CourseNana.COM

commit; CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

r3(B); CourseNana.COM

r3(B+6); CourseNana.COM

  CourseNana.COM

  CourseNana.COM

commit; CourseNana.COM

  CourseNana.COM

  CourseNana.COM

S2 CourseNana.COM

T1 CourseNana.COM

T2 CourseNana.COM

T3 CourseNana.COM

r1(A); CourseNana.COM

r1(B); CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

w1(B); CourseNana.COM

  CourseNana.COM

  CourseNana.COM

abort; CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

r2(A); CourseNana.COM

w2(A*4) CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

r3(B); CourseNana.COM

w3(B+6); CourseNana.COM

  CourseNana.COM

  CourseNana.COM

  CourseNana.COM

3: Querying XML Data (20 Marks) CourseNana.COM

Q3       In this task, you are required to use the below DTD to create an XML document and retrieve data from the XML document using Xpath/XQuery expressions. CourseNana.COM

<!DOCTYPE Sections [ CourseNana.COM

    <!ELEMENT Sections(Section*)> CourseNana.COM

    <!ELEMENT Section(Number,Year,Qtr,Course_number,Course_name,Student*)> CourseNana.COM

    <!ELEMENT Student(Ssn,Name,Class,Grade)> CourseNana.COM

    <!ELEMENT Number(#PCDATA)> CourseNana.COM

    <!ELEMENT Year(#PCDATA)> CourseNana.COM

    <!ELEMENT Qtr(#PCDATA)> CourseNana.COM

    <!ELEMENT Course_number(#PCDATA)> CourseNana.COM

    <!ELEMENT Course_name(#PCDATA)> CourseNana.COM

    <!ELEMENT Ssn(#PCDATA)> CourseNana.COM

    <!ELEMENT Name(#PCDATA)> CourseNana.COM

    <!ELEMENT Class(#PCDATA)> CourseNana.COM

    <!ELEMENT Grade(#PCDATA)> CourseNana.COM

]> CourseNana.COM

  CourseNana.COM

Q3 (a): Create a well-formed XML document corresponding to the above DTD. Provide at least three instances for multiple elements. (5 marks)  CourseNana.COM

Q3(b): Write queries in XQuery on the XML document you created in the previous step to do the following. For each query, provide query expression and code explanation. Execute each query and provide its results. (5*3=15 Marks) CourseNana.COM

  CourseNana.COM

4: Object-Relational Database (20 Marks) CourseNana.COM

Answer questions 4(a), 4(b), and 4(c) by considering the following scenario: CourseNana.COM

Suppose we want to develop a university teaching system to keep track of instructors and their teaching load. Each instructor has an office number and a unique name. Each instructor can only be either a Professor or a Teaching Assistant.  A professor is identified by rank (lecturer, associate, or full professor), and a Teaching Assistant is identified by the number of years they have been assisting a course. A course is offered by a department and identified by a unique course name. A course can have multiple projects, whereas each project must belong to a specific course. An assistant can have a teaching load of at least three and at most six courses, whereas each course may have at most two assistants. A professor may teach only two courses, whereas a course must have at least one and at most three professors. CourseNana.COM


CourseNana.COM

Q4(a)    Create class models to directly map the entities and relationships shown in the above UML diagram to objects. Provide complete code for each model class in a clear readable format and explain any assumptions made. Provide a screenshot of the database structure (tables) created in SQLite as a result of running the model classes. (7 marks) CourseNana.COM

Q4(b)   After defining all the model classes in the previous question, create ‘write.py’ file for defining methods to create objects/instances for populating all the tables in the database. For each table store at least five rows. Provide complete code for each method and a screenshot of each table populated with rows as a result of running the ‘write.py’ file. (7 marks) CourseNana.COM

Q4(c)    At this point, you have defined models and created objects. Now you will apply your skills of querying objects.  In this task, you are required to create ‘Read.py’ file under your project directory. Create code to perform the following queries: (2*3= 6 marks) CourseNana.COM

  CourseNana.COM

5: Data Warehousing and OLAP (20 Marks) CourseNana.COM

Q5 (a): A data warehouse can be modelled by either a star schema or a snowflake schema. Briefly describe the similarities and the differences of the two models, and then analyze their advantages and disadvantages with regard to one another. Give your opinion of which might be more empirically useful and state the reasons behind your answer. (5 marks)            CourseNana.COM

Q5(b): In this task, you will perform “Online Analytical Processing” (OLAP) style queries over a simple “star schema” given below. The corresponding schema, along with the database, is provided with this assignment. You are required to load the schema in Oracle Database and use SQL developer to perform the following queries: (5*3=15 marks ) CourseNana.COM

  CourseNana.COM


CourseNana.COM

  CourseNana.COM

       i.         Starting with the base cuboid [Item, Store, Customer, TimeDim], what specific OLAP operations should one perform in order to list the total sales for Item Category “Computer Accessories” for US stores in the Year 2015? Describe the operations and illustrate the results of each function on the above multidimensional data warehouse. CourseNana.COM

     ii.         Summarize (SUM, MIN, and COUNT) store sales for Canada and USA in 2014 by store zip code and year without using subtotal operators. Provide query code and results of executing the query. Illustrate your results to discuss the total number of group-by’s and why or why not the query is effective for information processing assuming huge data in the warehouse.  CourseNana.COM

    iii.         Use the same problem from part one and generate all possible subtotals by zip code and year using the data CUBE operator. Provide query code and compare your results with the previous question by comparing and contrasting results based on the number of rows and subtotals groups. Explain why or why not each type of subtotal is useful for supporting business-specific queries. CourseNana.COM

Get Expert Help On This Assignment

Scan above qrcode with Wechat

DTS207TC代写,Database Development and Design代写,Triggers and Indexing代写,Transaction Management代写,Querying XML Data代写,Object-Relational Database代写,Data Warehousing and OLAP代写,SQL代写,Python代写,XJTLU代写,DTS207TC代编,Database Development and Design代编,Triggers and Indexing代编,Transaction Management代编,Querying XML Data代编,Object-Relational Database代编,Data Warehousing and OLAP代编,SQL代编,Python代编,XJTLU代编,DTS207TC代考,Database Development and Design代考,Triggers and Indexing代考,Transaction Management代考,Querying XML Data代考,Object-Relational Database代考,Data Warehousing and OLAP代考,SQL代考,Python代考,XJTLU代考,DTS207TChelp,Database Development and Designhelp,Triggers and Indexinghelp,Transaction Managementhelp,Querying XML Datahelp,Object-Relational Databasehelp,Data Warehousing and OLAPhelp,SQLhelp,Pythonhelp,XJTLUhelp,DTS207TC作业代写,Database Development and Design作业代写,Triggers and Indexing作业代写,Transaction Management作业代写,Querying XML Data作业代写,Object-Relational Database作业代写,Data Warehousing and OLAP作业代写,SQL作业代写,Python作业代写,XJTLU作业代写,DTS207TC编程代写,Database Development and Design编程代写,Triggers and Indexing编程代写,Transaction Management编程代写,Querying XML Data编程代写,Object-Relational Database编程代写,Data Warehousing and OLAP编程代写,SQL编程代写,Python编程代写,XJTLU编程代写,DTS207TCprogramming help,Database Development and Designprogramming help,Triggers and Indexingprogramming help,Transaction Managementprogramming help,Querying XML Dataprogramming help,Object-Relational Databaseprogramming help,Data Warehousing and OLAPprogramming help,SQLprogramming help,Pythonprogramming help,XJTLUprogramming help,DTS207TCassignment help,Database Development and Designassignment help,Triggers and Indexingassignment help,Transaction Managementassignment help,Querying XML Dataassignment help,Object-Relational Databaseassignment help,Data Warehousing and OLAPassignment help,SQLassignment help,Pythonassignment help,XJTLUassignment help,DTS207TCsolution,Database Development and Designsolution,Triggers and Indexingsolution,Transaction Managementsolution,Querying XML Datasolution,Object-Relational Databasesolution,Data Warehousing and OLAPsolution,SQLsolution,Pythonsolution,XJTLUsolution,