Can someone please help/advice/any idea my term project!

The term project for this course will be to design and constructa database that uses appropriate Transact SQL procedures andfunctions. The database can be of your choice. It can be a new typeof business or an existing business that you feel you could designa more functional database. With that said, I do require that thedata types, columns and tables have a unique naming convention.

The main purpose of this project is to gain hands on experienceon identifying business goals and rules in order to creatively andcritically think about feasible solutions. Once a high overviewobjective has been documented it will be required to identify theconstraints, columns, and tables that can be illustrated through anERD diagram. It is expected that your database will consist of atleast 8 tables and recommended that it has fewer than 20tables.

Term Project: Objective 1

At this stage you should have already identified your databaseideas and submitted them to the instructor for approval. Theobjective here is to illustrate, explain, and expand on youroverall pre-approved database concept. I have listed tasks thatshould help you filter out your ideas and help in identifying avisual (ERD) database design. The outcome of this part of the termproject is the documentation needed to create the database whichyou’ll be asked to do in the next term project assignment.

Complete the following:

  1. Select one of your database ideas that your instructor hasapproved.

  1. In a Word compatible document, provide a written description ofthe problem your database is intended to solve. Describe thesituation in which your database will be used. This description canbe similar to what you submitted previously but you may need to addmore detail or information.

  1. In the same Word document, create a list of the business rulesthat affect your database design. Keep in mind that business rulesthat specify many to many relationships between entities are fine.It is those business rules that imply an intersecting table will beneeded to resolve the many to many relationship. You will likelyneed to add to your business rules as your design progresses so besure to update your list of business rules to reflect any changesin your ERD.
  1. Use Visio or a similar product to create an ERD of yourdatabase design. Your database design should be normalized to atleast third normal form and include the following. It is expectedthat your database will consist of at least 8 tables andrecommended that it has fewer than 20 tables. The ERD shouldshow:
  • Table names
  • Primary and foreign keys
  • Identify cardinality for each relationship
  • Specify with action verbs the forward relationship in all oneto many relationships
  • Resolve all many to many relationships

  1. In the same Word document create your data dictionary as youdid in Exercise 2 the first week of class. The data dictionary hasthe following information which may be shown using a Microsoft Wordtables for each table in the ERD as was done in Exercise 2.
  • The columns names for each table. (If the business rules fromexercise 1 didn’t define appropriate columns for a specific table,add some columns that seem logical to you.)
  • Give a description for each column.
  • Specify the primary and foreign keys
  • Specify the data type and size for each column. Select sizesthat seem logical to you. Of course in a real world situation youwould talk with users or look at documentation to determine themaximum size for a given column.
  • Specify what columns that are not primary or foreign keys mustbe UNIQUE (it will be assumed all other columns don’t have to beUNIQUE).
  • Specify what columns that are not primary or foreign keyscannot be NULL (it will be assumed all other columns can be NULL).Your project must have some columns that fit this.
  • Specify any fields that should be indexed (frequently queriedattributes). Your project must have some indexes.
  • Specify default values for any columns where it seems to makesense. Your project must have some.
  • Add check constraints. For each constraint write a businessrule. Your project must have some.

You should specify most of the aboveusing Word tables as in Exercise 2. You could use something likethe following.

Column

Key

Unique

Not Null

Data

Size

Description

Name

Type

Column

Needs

Default

Check Constraints

Name

Index

Value

  • Specify Business Rules

You should specify the above bycreating a list that is either numbered or bulleted

Term Project: Objective 2

Now that you have your database designed (Objective 1), youshould be ready to develop your database through SQL coding. (Note:Your database should be based on your ERD and data dictionary thatyou developed in Objective 1.)

Through this process you may find that modifications to yourtables or columns may be necessary. If you make changes to tablesor columns be sure to modify your ERD, data dictionary and/orbusiness rules to reflect the changes. At the end of the term youwill be expected to hand in an up-to-date ERD, data dictionary andset of business rules.

Complete the following:

  • Write the SQL that creates the tables, indexes and sequencesfor your database. (Place all your SQL in the same scriptfile.)
  • Be sure to include all your constraints: primary keys, foreignkeys, non-primary key columns that should be not null, non-primarykey columns that should be unique, and check constraints. You musthave some columns that should not be null and some that have checkconstraints.
  • There must be at least one sequence within your database.
  • Include any default values (For example: date, zip code or areacode value) for columns specified in your design. You must havesome in your project.
  • Write the SQL statements that create the indexes you specifiedin objective 1. You must have some in your project.
  • Write SQL statements to INSERT at least 4 or 5 records intoeach of your tables. You may want more in some tables. You want tohave enough data that allows you to test queries and Transact SQLcode that will be developed later. For example you may want morethan 4 or 5 records in an intersecting table that exists to resolvea many to many relationship between two tables.
  • Write SQL statements that verify the data has been insertedinto your tables
  • Test your SQL thoroughly before handing it in to theinstructor.

Term Project: Objective 3

Now that you have written your SQL for your tables, constraints,indexes, and sequences and inserted some data (Objective 2), youneed to create procedures, functions and triggers. All of theseneed to be well commented so that it is apparent by reading thecomments what the purpose of the procedure, function, or triggeris. Read all of the tasks below before starting.

  • Create a total of at least six procedures and functions. Atleast two of these six program units must be functions and at leastthree must be procedures.
    • If you find that you did not INSERT enough data into yourtables in Objective 2 to test these procedures and functionsproperly, INSERT more data as it is needed.
    • The program units cannot just do more of the same otherprocedures. That is they can do an INSERT, UPDATE or DELETE for thethree procedures but if you wanted to add a fourth procedure thenit would have to be more complex. For example if one procedure didan update, the second ‘update’ procedure must contain otherprogramming code (such as an IF ELSIF or loop structure) that fitsthat context.
  • There are many options for the two or more functions. Forexample a function could return the zip code given the customer id.Or a function could return the health insurance carrier given thepatient id.
  • At least three out of the six program units must containEXCEPTION handling. For instance a ‘try…catch’ blocks.
  • In addition to creating the six program units, you need tocreate at least three triggers. One of the triggers should fire onan INSERT, one should fire on an UPDATE and the third should fireon a DELETE. For example you could create triggers that correspondto the INSERT, UPDATE and DELETE procedures mentioned above. Thetriggers could add information to a log table that is used to trackchanges to one or more tables.

The set of procedures, functions, and triggers you create is notexpected to be an entire software application. You only need tomeet the specifications above in this document. For example eventhough there would be many procedures to manage the data in all thetables, you do not have to write the code for them. You only needto write the code for the six program units and the three triggers.Be sure to thoroughly test all of your procedures, functions andtriggers.

Upload a script file that contains the procedures, functions andthree triggers. In addition upload the latest version of yourscript that creates your database. It is needed to test yourprogram units. Also if you have altered your documentation inObjective 1 since you turned it in, upload the latest copy of it aswell.