Lab 2 Implementation:
Importing Data and Using Views

The Assignment

Read all instructions below at least once before attempting to implement these requirements. In this assignment, your task is to import the data you analyzed during the Design phase. You will use the physical design that was selected by your colleagues (i.e. by your classmates and instructors) during the classroom design discussion. This final physical design will be made available on Canvas (Lab 2 Implement) soon after the classroom discussion.

Database Maintenance and Lab Skills

Database Maintenance
  • Team work log: download your team work record log file from assignment Lab2d-LM&PM and record the team activities
  • No previous DB maintenance
Lab Skills
  • Check the links at left to "Create Identity PKs" and "Create Views". You will need these to build your tables and views correctly.
  • All deliverables must follow the ...
    • Naming Conventions,
    • Diagramming Guidelines,
    • 421 Data Specifications, and
    • SCA Submission Guidelines
      (see the menu at Left).
  • [req #s] indicate required deliverables
  • Use Quality Assurance figures (below) to {at least partially} verify that your deliverables have been completed correctly.

Analyst Notes & Deliverables Plan

  • Your Team Database:
    Your team database has been created on Yorktown. Any individual practices and experiments should be done in each student's individual database that has this name format: QYY_MIS421_IndividualDB_YourLastname_YourFirstname, where Q is the quarter abbreviation (F-Fall, W-Winter, S-Spring, SUM-Summer)
     
  • Examine and prep your data: follow the instructions in the following link
    Importing Data - Preparation of Data
     
  • Import your Data (Download the Excel workbooks: SCASisterCities.xlsx and SCAPersons.xlsx from Lab 2 Design of SCA Database Project site):
    Throughout the quarter, you will import new data into your SCA database by using the SQL Server Import and Export Wizard. The final tables must accurately reflect the ER diagram that was approved and released on Canvas (created in MySQL Workbench and uploaded to this assignment in Canvas). The tables must also conform with the the Naming Conventions and the 421 Data Specifications. (See the menu at left.)

    The steps for importing data may differ, depending on the data type or the size of the data. The steps below (3c and 3p) describe two different import methods; one method is appropriate for importing large strings and for integers. The other method is approriate for data that looks like a number, but is actually a string of characters (e.g. as a phone number). All of the data that you will be given this quarter can be imported by using the steps described in 3c or 3p. Always analyze the data and choose the appropriate method before you attempt to import the data.
    3c. [req 2.10] Import "SCASisterCities.xlsx" - Data w/integers & large strings (See menu at left.)
    • Quality Assurance: [req 2.10] 6 records.

    3p. [req 2.20] Import "SCAPersons.xlsx" - Data w/strings resembling #s (See menu at left.)
    • Quality Assurance: [req 2.20] 59 records.

    Modify the design of the newly created SQL Server tables (SisterCity and Person). They must meet the 421 Data Specifications and naming conventions.
    4. [req 2.25]
    • Download the lab 2 final physical data model in Assignment L2i-DB from Canvas
    • Based on the physical model, finalize the import tables. (See instructions in 'Import: finalizing imported tables' in the menu at left.)
    • Create an identity field as the PK of each table. Study How to create identity PKs (link at the left menu) before you start
    • Quality Assurance: [req 2.25] Make sure the columns are consistent with the columns in the physical data model. Data types and sizes in both new tables match 421 Design Standards. Examine the data to ensure no data was corrupted during importation.
  • Create Views. Create the following views.  The view and field names need to conform to the Naming Conventions. Please study How to Create View (link at the left menu) for creating a view using a GUI before you begin.
    NOTE: Soon you will learn a better way to create a view -- using SQL code instead of a GUI. 
    • [req 2.30] A view that returns all persons. The output should include the person's first, middle and last names, membership type, and email address.
      Quality Assurance: 59 rows and 5 columns returned.
    • [req 2.40] A view that returns all persons who hold individual or student memberships. The output should include the person's first, middle and last names, membership type, and email address.
      Quality Assurance: 40 rows and 5 columns returned.
    • [req 2.50] A view that returns a list of all medium-size sister cities – “medium size” is defined as greater than 50,000 and less than 100,000. Display 2 columns: the city name and the population.
      Quality Assurance: 3 rows and 2 columns returned.
Submission:
  • Submit to Canvas your most recent Team Work Record . (submit to assignment L2i-DB)

End of Instructions