Chat with us, powered by LiveChat <p | Study Help
  

ISM530 – Task 4

Create these tables, insert data and make query based on the followings, please include screenshots for table creation, data insertion and query to retrieve the data:

Here is what I would like to have for our data warehouse. Following up on our GIS theme, our datawarehouse is to be used to trace contacts that a person had with others, for use with tracking folks who have a contagious disease.

For our database, we will model a small piece of a small town.

The town will have people, so we need a table for person, we can call it person. The table needs to have a primary key (int), firstname, lastname, vaccination date, gender, birthday, street number, street ID, business ID, school ID. This gives us a person table with lots of links to other tables.

We need a business table, That will have a business ID, business name, street number, street ID, owner ID (which is a link to person table).

We need a home table. That will have a home ID, street number, street ID, square footage. (extra fields could be added here but probably not needed)

We need a school table. That will have a school ID, street number, street ID, name, lower grade, upper grade, number of classrooms

We need a street table. That will have a street ID, direction north/south or east/west, starting number, ending number, number of lanes

Once we have the tables made and populated, we will want to find all the people who live at an address, all the people who work at a business, all the people who attend a school. Now folks could work at a school or attend a school.

The fields above that I called ID are just the primary key. Most databases will allow for an autonumber, which ensures that the records have a unique primary key.

For our prototype, we need to put in some number of records and setup the relationships for those to show for example that a person works at a certain business and lives at a certain house. These keys for person should be done after the other tables are built, because person really holds the whole schema together via the primary/foreign keys.

Right now, I figure we need at least 15 homes, 25 people, 10 businesses, 3 schools, and 10 roads.

Work on the design right now, and we will work on populating the tables once you have the design put together.

(See if your database will support import from an excel file or other comma delimited file to make life easier.)

Rubrics:

Prototype of the Warehouse: Presents a functional prototype of the warehouse with data, sample queries, and documentation on requirements. The prototype while functional for testing is not a final model that has been programmed but is ready for programming in SQL. Includes results of collection of feedback from stakeholders on prototype.

Data Warehouse Test Plan and Results Document: Describes the test plan to validate the data model with the customer and determining if the model works as expected, and if it comprises all of the required data. Includes details of the plan along with the results documentation for testing of the prototype with stakeholders. An analysis of the results is included along with recommendations for changes.

Resources:

https://www.linkedin.com/learning/learning-microsoft-business-intelligence-stack/welcome?u=56747577

http://prx-stratford.lirn.net/login?qurl=http://library.books24x7.com/library.asp%3f^B%26bookid=53708

error: Content is protected !!