CIDM 6350 WTAMU Database Management Good & Bad Database Design Worksheet

User Generated

zbunzzrqabbev

Computer Science

CIDM 6350

West Texas A & M University

CIDM

Description

Hi, I would like to receive detailed answers on the attached questions. Need only any 4 questions answered out of the 6. References are also attached. I need to this to be delivered on 4/20/2021, at 9:00 PM PST.

Unformatted Attachment Preview

N.B: Answer any FOUR questions Q1. [50 points] Functional Dependency (FDs) a. [ 15 points] What are properties of a good/bad database design? What is a functional dependency (FD)? What are the possible sources of information that defines the FDs that holds among the attributes of a relational schema? [ Hints : see from lecture slides] b. [ 20 points] Ruling Out FDs: Given a relation “MyTinyStore”. Which FDs may exist in this relation? What do you need to rule out? MyTinyStore Customer_LastName Smith Smith Paul Sara Drink_Item Iced_Tea Coke Coke Coffee Food_Item Burger Hot Dog Hot Dog Noodles Delivery_method Not_Delivery Delivery Carry out Carry out c. [15 points] Consider the two relations : account and customer. Using SQL commands show that how a functional dependency can be derived from single key and multi-column keys. [ Hints: see from https://dev.mysql.com/doc/refman/5.7/en/group-by-functional-dependence.html ] Also, explain how functional dependencies can be used to indicate the following: (i) A one-to-one relationship set exists between entity sets account and customer. (ii) A many-to-one relationship set exists between entity sets account and customer. Q2. [50 points] Normalization a. [ 15 points] What does the term unnormalized relation refer to? How can we develop normal forms from the 1NF to BCNF. b. [ 25 points] Consider the schema Customer (order file figure 9-1). Is this schema normalized? If yes, explain. If not, normalize it and explain. [ Hints: you can decompose the table to multiple tables if necessary] 1 c. [ 10 points] Briefly explain about multi-valued dependency, and higher normal forms (e.g. 4th normal form) Q3: [ 50 points] Data stores and indexing a. [15 points] What do you mean by database indexing? Write some SQL commends to index using B+tree, and for hashing, encryption, and compression functions. b. [20 points] A PARTS file with Part# as the key field includes records with the following Part# values: 20, 69, 37, 61, 47, 92, 48, 71, 56, 59, 18, 21, 10, 74, 78, 15, 16, 20, 24, 28, 39, 43, 47, 50, 69, 75, 8, 41, 33, 38. Suppose that the search field values are inserted in the given order in a B+-tree of order pleaf = 4; Try to show how the tree will expand. What the final tree will look like (extra!). [ Hints you can use visualization tool mentioned in lecture slide] c. [15 points] Compare different indexing techniques based on their advantages and disadvantages. Q4. [50 points] Database Transactions Processing a. [15 points] What do we mean by a serializable schedule in a database transactions processing? Or, “Serializable schedule of n transactions is equivalent to some serial schedules of same n transactions” – explain with an example. a. [ 20 points] Consider the three transactions T1, T2, and T3 and four schedules S1, S2, S3, and S4 as below. Which of the following schedules is (conflict) serializable? For each serializable schedule, determine draw the precedence graph. [ Hints : you can use the software referred in the lecture slide] S1: r1(X); r3(X); w1(X); r2(X); w3(X); S2: r1(X); r3(X); w3(X); w1(X); r2(X); 2 S3: r3(X); r2(X); w3(X); r1(X); w1(X); S4: r3(X); r2(X); r1(X); w3(X); w2(X); d. [ 15 points] Write at least five points on the importance of transactions processing in a business databases? Also, Briefly explain how to implement a database transaction processing using SQL? [ Hints : see from https://www.geeksforgeeks.org/sql-transactions/] Q5. [50 points] OODB, NoSQL, MongoDB a. [ 20 points] Write the similarities and differences in SQL implementation of an OO schema for a database application and the traditional database implementation (you can consider the UNIVERITY database as an example). [ Hints : You can explain, how to construct an EER schema for the application, and then create the corresponding classes in ODL. Specify a number of methods for each class, and then specify queries in OQL for your database application, etc..] b. [20 points] What are advantages of using NoSQL database? Name some industries (five) who adopted the No SQL database environment. Explain the MongoDB data model with an example. c. [10 points] Can we run query efficiently in MongoDB? Give an example. [ Hints : for mongoDB see document and links in the test/final folder] Q6. [50 points] Distributed DB, Big-Data, MongoDB, Block chain a. [ 15 points] The Clustrix Database is a distributed database built from the ground up to be a MySQL replacement. It has a shared nothing architecture and automatically distributes data and does distributed query evaluation. Base don Clustrix as an example, briefly explain how to build a distributed database using SQL. [Hints: https://docs.clustrix.com/display/CLXDOC/Data+Distribution ] [Hints : You can populate it as a table format, give an example how will it be represented ? base representation k1 representation k2 representation, and show the base representation slices and clusters. ] b. [15 points] Map and Reduce automatically parallelize and executes on large clusters of commodity hardware. Briefly explain the MapReduce programming model with an example. [ Hints: see lecture slide] c. [20 points] What is a block chain technology? How does the blockchain technology upending data transaction processing only as secure as its infrastructure? Explain with an example. [ Hints: see lecture slide] 3 4
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

View attached explanation and answer. Let me know if you have any questions.

Question One
Properties of a Good Database Design
1.
2.
3.
4.
5.
6.
7.

Presents all the projected Data over time.
Easy to understand
Allows access to all the data
The problem at hand is presented in real- world structure
Avoids duplication of data
Allows access of multiple users
Allows easy maintenance of data

Properties of bad database design
1.
2.
3.
4.
5.
6.

Data integrity not protected
Poor documentation
Normalizing of data is ignored
Poor design
Weak naming standards
Reference data not stored properly

Functional dependency- in database management, functional dependency is a constraint that
determines the relationship of one attribute to another. It’s used in maintenance of quality data.
Possible sources of information in functional dependency
1. Reflexivity
2. Augmentation
3. Transitivity
c. MyTinyStore
Customer_LastName Drink_Item
Food_Item
Delivery_method
Smith
Iced_Tea
Burger
Not_Delivery
Smith
Coke
Hot Dog
Delivery
Paul
Coke
Hot Dog
Carry out
Sara
Coffee
Noodles
Carry out
We find out whenever a customer takes coke (as a drink) then he/she takes Hot Dog as a food item.
Therefore drink item attribute uniquely identifies the food item. If we know the drink item we can
know the food item he will take.
Therefore food item is a functional dependent of the drink item
Drink item > Food item

Question 5
Differences/similarities between SQL implementation and the traditional methods
SQL implementation – process of installing a database software, configuring, customizing, testing,
running, testing integrating and thereafter training the users.
Procedure.
1.
2.
3.
4.
5.
6.

Install the DBMS
Setup the variables
Creation of database and tables
Loading the data
Setting up users and security features
Implementation of backup

Traditional database methods – they were designed to store correlational records
They are the:
a) Relational mode
b) Hierarchal model
c) Network model
Advantages of the NOSQOL database
1.
2.
3.
4.
5.
6.
7.

Can store unstructured, semi-structured or structured data.
Friendly to the developer
Can take advantage of the cloud so as to deliver zero downtime
Can handle huge volumes of data
Requires no advanced expertise
Can be scaled
Requires less support

QUESTION 2
Unnormalized relation – it’s also called a NON – first normal form (NF2). It contains repeating groups
and entities do not need to be first singled out and satisfies all the properties of a relation.
It therefore lacks the efficiency of a database normalization.

Developing normal forms from 1NF to BCNF.
Normalizing a database is reducing the data redundancy and eliminating not so desirable char...

Related Tags