In your new job at WebbedMD, you have the
task of obtaining data from the U.S. Food and Drug Administration
(FDA) for use on a new website:
Generic & Brand Name Drug Selector. Your paramount
responsibility is data integrity. You also have responsibility
for creating a database that efficiently allows appropriately
authorized users access to data for the following business
needs:
-
Your first step is to obtain your
information
from the FDA. This page describes the purpose of this site;
you will find your data files under the link entitled "Information and Data Files." You
see the schema description as well as a link entitled "EOBZIP.EXE."
Download this zip file and unzip it to obtain the three text files.
These can then be imported by each of the following methods:
-
Using the schema description on the FDA website,
illustrate and submit a relational model
(1) that will hold the data in the three text
files. Use Figure 3.2 in the course book as your reference for
a relational model and
identify the Relation name, Attribute, Domain Name, Meaning and
Domain Definition.
-
Create a database using a DBMS package.
All lab submissions can come from the DBMS package of your
choice. The CEC offers Microsoft Access and, via
http://webdev.cec.wustl.edu, Microsoft SQL Server. The
instructions for this course are for SQL Server, but the
instructor will assist you if you plan to use another DBMS
package.
-
Create tables in your database to match your
relational model. Create your first table for the Products
data by
writing a SQL Data Definition Language statement using:
CREATE TABLE...
Submit this statement (2).
Your statement should be similar to the syntax
format on p 170. You will not use all
of the clauses illustrated on p170 yet, but you can try writing them
and see what happens. If you want to delete a table after
creating it, enter "DROP tablename" and run the query. You should
have a statement that includes table name, column names and data
types for this point in the course.
-
Run your DDL using Microsoft Query Analyzer.
-
Once you create the table, open Enterprise Manager,
connect to your database and view your table in the GUI view.
Import the data in Products.txt by using the BULK INSERT
command in Query Analyzer. To learn the correct syntax for BULK
INSERT, open SQL Server Books Online and type in BULK INSERT in the
Index Interface. When you click on BULK INSERT (described),
you will see a description of the command and an example of the
syntax at the bottom of the page.
-
Now import your remaining two text files, but by
using the following steps:
-
In Enterprise Manager, find and activate the Import
Wizard. Use it to create the two tables and import the data.
-
When you have successfully imported the data, check
the number of records by running the following in Query Analyzer for
each table:
SELECT * FROM [table name]
-
You will see a display of the number of records
returned, submit that number, along with your
query, for each table (3).
-
Also submit the number of records returned,
and the query, for the following DDL statement for each table
(4):
SELECT DISTINCT (*) FROM [table name]
-
Now use the left panel of Enterprise Manager and
click until you see your tables in the right panel (you may have
to click Refresh after each import). Right-mouse click on
a table name and select "Design Table." You will see your
columns and data types. The Import Wizard creates a
default column name and data type when those are not specified.
If you have not already changed these defaults, modify them to
match any schema information available from the FDA webpage.
-
Evaluate your data by using each of the
following queries (replacing the terms in brackets with your own
terms):
SELECT DISTINCT COUNT( [column name 1] ) FROM [table
name] WHERE [column name 1] IS NOT NULL
-
Repeat this for each column name and
submit the query and the
value for each query (5).
DATABASE MANAGEMENT SYSTEMS (CSE530)