Question

(Subject Database)

Q1. Define, compare and contrast Conceptual, logical and physical database design

Q2. What are the data elements necessary to create an invoice for a customer? Save this query as a view named Invoice View

Q3. Create an alphabetical index on customer name in the Customer table.

Q4. Prepare a report that evaluates possible client/server solutions to handle new customer application system for all branch offices? What technology characteristic you will evaluate?

Q5. What is a query optimizer in SQL? How is it different that SQL Query

Q6. Create a star schema for your project.

Q7. How can you benefit from the concepts of date warehousing and DataMart? Is it visible to use it for your project? Why?

Q8. What do you think about the quality of the data in your project?

Q9. Since there are many recovery techniques available, explain which of the recovery techniques is most appropriate for your project, and why.

Q10. What is cloud computing, is it viable to use it for your project?

Answer 1

Ans1. The Conceptual data design defines WHAT the system contains. This model is typically created by Business stakeholders and Data Architects. The purpose is to organize, scope and define business concepts and rules.

A Logical data design is a fully-attributed data model that is independent of DBMS, technology, data storage or organizational constraints. It typically describes data requirements from the business point of view. While common data modeling techniques use a relational model notation, there is no requirement that resulting data implementations must be created using relational technologies. It defines HOW of the System.

A Physical data design is a fully-attributed data model that is dependent upon a specific version of a data persistence technology. . The purpose is actual implementation of the database.

Difference of these 3 are given below:

Properties	Conceptual data design	Logical data design	Physical data design
Entity Names	YES	YES	NO
Entity Relationships	YES	YES	NO
Attributes	NO	YES	NO
Primary Keys	NO	YES	YES
Foreign Keys	NO	YES	YES
Table Names	NO	NO	YES
Column Names	NO	NO	YES
Column Data Types	NO	NO	YES

YES means the data design contains the properties and NO means data design doesn't have the properties.

Ans2. The elements needed are Customerid, Customeraddress, orderid, productid, productprice, orderquantity

Query is given by,

CREATE VIEW INVOICEVIEW AS

SELECT Customer.Customerid, Customeraddress, Order.orderid, Product.productid,productprice, orderquantity

FROM Customer, Order, Orderline, Product WHERE Customer.Customerid=Order.Customerid

AND Order.orderid=Orderline.orderid

AND Product.productid=Orderline.productid;

all these should be in a single line.

Ans3.

CREATE UNIQUE INDEX A
on Customer (customername);

This query creates index for the column name of a given table. Its syntax is :

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Ans5. The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans while a sql query is a request for information from a database .