Question

Guidelines:

The final project (Project 3) is an individual project. It requires writing an individual report and a presentation. Your manager at the VPF wants your help to answer the following questions and form your reply as a complete report. The report should be between 5 and 7 single spaced pages in addition to the cover page, table of contents, statement of Academic Honesty, reference page, and any other diagrams or appendices that will enhance your report.

Note: Use the Pine Valley database as a reference for this project (projects 1 and 2)

After completing projects one and two, your manager asked you the following top ten questions. You should answer and report back to your upper management :

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? Why?

Project 3 Part 1-Presentation:

All Presentations will be given during the scheduled time in December. The time for each presentation is between 2-3 minutes. Please see the course announcement for a schedule of all presentations. Presentations are worth up to 25 points.

Project 3 Part 2-Report:

Each student will write a comprehensive report about the topic presented in the class. Make sure to submit a quality report which includes the managerial and technical information. The report should be between 5-7 single-spaced pages. (Note the cover page, Table of Contents, and references do not count from these 5-7 pages)

Pine Valley Furniture Company Databases

The Modern Database Management, 13th edition web sites for both instructors and students contain two versions (called ‘Book’ and ‘Expanded’) of Pine Valley Furniture Company (PVFC), based on Figures 1-13, 2-22 and 5-6 of the textbook. These figures show a subset of the attributes available in the databases.

The files for the book version, covered here, contains the tables of Figure 2-22, along with much of the sample data used in Chapter 1, 5, and 6.

Book Pine Valley Furniture Company (PVFC) Datasets

Customer_T

CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode

1, 'Contemporary Casuals', '1355 S Hines Blvd', 'Gainesville', 'FL', '32601-2871'

2, 'Value Furniture', '15145 S.W. 17th St.', 'Plano', 'TX', '75094-7743'

3, 'Home Furnishings', '1900 Allard Ave.', 'Albany', 'NY', '12209-1125'

4, 'Eastern Furniture', '1925 Beltline Rd.', 'Carteret', 'NJ', '07008-3188'

5, 'Impressions', '5585 Westcott Ct.', 'Sacramento', 'CA', '94206-4056'

6, 'Furniture Gallery', '325 Flatiron Dr.', 'Boulder', 'CO', '80514-4432'

7, 'Period Furniture', '394 Rainbow Dr.', 'Seattle', 'WA', '97954-5589'

8, 'California Classics', '816 Peach Rd.', 'Santa Clara', 'CA', '96915-7754'

9, 'M and H Casual Furniture', '3709 First Street', 'Clearwater', 'FL', '34620-2314'

10, 'Seminole Interiors', '2400 Rocky Point Dr.', 'Seminole', 'FL', '34646-4423'

11, 'American Euro Lifestyles', '2424 Missouri Ave N.', 'Prospect Park', 'NJ', '07508-5621'

12, 'Battle Creek Furniture', '345 Capitol Ave. SW', 'Battle Creek', 'MI', '49015-3401'

13, 'Heritage Furnishings', '66789 College Ave.', 'Carlisle', 'PA', '17013-8834'

14, 'Kaneohe Homes', '112 Kiowai St.', 'Kaneohe', 'HI', '96744-2537'

15, 'Mountain Scenes', '4132 Main Street', 'Ogden', 'UT', '84403-4432'

Order_T.

OrderID, OrderDate, CustomerID

1001, '21/Oct/10', 1

1002, '21/Oct/10', 8

1003, '22/Oct/10', 15

1004, '22/Oct/10', 5

1005, '24/Oct/10', 3

1006, '24/Oct/10', 2

1007, '27/Oct/10', 11

1008, '30/Oct/10', 12

1009, '05/Nov/10', 4

1010, '05/Nov/10', 1

Product_T.

ProductID, ProductDescription, ProductFinish, ProductStandardPrice, ProductLineID

1, 'End Table', 'Cherry', 175, 1

2, 'Coffee Table', 'Natural Ash', 200, 2

3, 'Computer Desk', 'Natural Ash', 375, 2

4, 'Entertainment Center', 'Natural Maple', 650, 3

5, 'Writers Desk', 'Cherry', 325, 1

6, '8-Drawer Desk', 'White Ash', 750, 2

7, 'Dining Table', 'Natural Ash', 800, 2

8, 'Computer Desk', 'Walnut', 250, 3

OrderLine_T.

OrderID, ProductID, OrderedQuantity

1001, 1, 2

1001, 2, 2

1001, 4, 1

1002, 3, 5

1003, 3, 3

1004, 6, 2

1004, 8, 2

1005, 4, 4

1006, 4, 1

1006, 5, 2

1006, 7, 2

1007, 1, 3

1007, 2, 2

1008, 3, 3

1008, 8, 3

1009, 4, 2

1009, 7, 3

1010, 8, 10

Answer 1

ANSWER:

(1) The three level of data modeling:-

conceptual data model

A conceptual data model identifies the highest-level relationships between the different entities. Features of conceptual data model include:

·Includes the important entities and the relationships among them.

·No attribute is specified.

·No primary key is specified.

Conceptual data models known as Domain models create a common vocabulary for all stakeholders by establishing basic concepts and scope.

logical data model

A logical data model describes the data in as much detail as possible, without regard to how they will be physical implemented in the database. Features of a logical data model include:

·Includes all entities and relationships among them.

·All attributes for each entity are specified.

·The primary key for each entity is specified.

·Foreign keys (keys identifying the relationship between different entities) are specified.

·Normalization occurs at this level.

The steps for designing the logical data model are as follows:

1. Specify primary keys for all entities.

2. Find the relationships between different entities.

3. Find all attributes for each entity.

4. Resolve many-to-many relationships.

5. Normalization.

The advantage of the Logical data model is to provide a foundation to form the base for the Physical model. However, the modeling structure remains generic.

physical data model

Physical data model represents how the model will be built in the database. A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Features of a physical data model include:

Specification all tables and columns.
Foreign keys are used to identify relationships between tables.
Denormalization may occur based on user requirements.
Physical considerations may cause the physical data model to be quite different from the logical data model.
Physical data model will be different for different RDBMS. For example, data type for a column may be different between Oracle, DB2 etc.
The steps for physical data model design are as follows:

Convert entities into tables.
Convert relationships into foreign keys.
Convert attributes into columns.
Modify the physical data model based on physical constraints / requirements.

This type of Data model also helps to visualize database structure. It helps to model database columns keys, constraints, indexes, triggers, and other RDBMS features.

2) Ans:

Invoice reports are master/detail reports with billing amounts that print conditionally. The customer name, address, and related information are derived from the master query (or group, if there is only one query). The line–items come from the detail query. The billing amounts are printed in the page footer.

This information must all print on specific line and column positions on the preprinted invoice form

Data Relationships

The data for this report is fetched using two queries, linked together in a master/detail relationship. You will also define a break and two summaries.

Layout

This layout is similar to the layout for the preprinted form report, but in this case you will also link to a TIFF image of your form to use as a guide while adjusting the positions of several fields.

you will create a report that prints an invoice for each customer, listing the following information:

standard billing information such as name, address, date, purchase order number, and so on

a list of items purchased, including item number, description, and price

the total cost of all items purchased

Required steps are as follows:

Create a new report manually to create a new, empty report.

Create a data model with a data link to create two queries with a data link between them.

Create summary and formula columns to include on the invoice.

Prepare the layout for inserting the invoice information..

Insert invoice information by creating new fields for the invoice information, positioning them in the correct locations on the invoice form.

ques:3)Ans:

CREATE INDEX Name_IDX ON Customer_T(Customer_Name);

Que:5Ans:

A query optimizer is a critical database management system (DBMS) component that analyzes Structured Query Language (SQL) queries and determines efficient execution mechanisms. A query optimizer generates one or more query plans for each query, each of which may be a mechanism used to run a query. The most efficient query plan is selected and used to run the query.

Database users do not typically interact with a query optimizer, which works in the background.

SQL queries may be simple or complex statements. Each SQL statement requires minimal use of valuable resources, such as disk reads and server memory. The query optimizer ensures this, as well as expedited execution of each SQL query. For example, a query optimizer may generate a series of query plans based on resource costs. One query plan may involve reading a table to retrieve a subset of its data, while another may involve using table indexes for quick data reading. These are known as cost-based optimizers.

A query optimizer may select different query plans for the same query, depending on environmental circumstances. For example, a user runs a query that selects approximately half of a table's data. The user runs the query when the server is heavily tasked with multiple simultaneous connections. In this scenario, the query optimizer may decide to use a query plan that calls on the created table indexes to satisfy the query, based on limited resources. This ensures minimal server drain by the query. By running the same query at another time with more resources, the query optimizer could determine that resource limitation is not an issue. In this instance, table indexes would not be used, and the query optimizer would allow full table loading to server memory.