READ: The (downloadable) Access1 Powerpoint presentation "Introduction to a Relational DBMS: Microsoft Access"  and Appendix B, pp. 629-635, & VideoTape 3.
 
     In a personal interview with Snookie Notebook Computers, Inc.,  CEO Joseph Nanna provides to you the following discussion of his database needs and future plans:

     Snookie Notebook Computers is going to be a 'customer driven' company, and our database will play an integral role in this regard. All customers who call on us will be entered into our database, and assigned a (single) personal sales representative who will always work with the customer to satisfy his/her needs, throughout their relationship with Snookie. Our sales representatives go thru an intense training program, to fully acquaint them with our products, and the art of dealing with customers. All sales representatives, incidentally, will also be in our database (even if they are in our training program and have not yet dealt with any of our customers).
 
     Although I am just beginning my web-based notebook computer direct sales business with only a few customers, I anticipate steady growth and recommendations and I can optimistically foresee having several thousand satisfied customers over the short term. I  have my corporate offices in the State of New Jersey, and I anticipate also that about 95% of my future business will be in New Jersey, though my current customers are friends from nearby states. However, I am planning an aggressive advertising campaign in the nearby states of New York, Pennsylvania,  Delaware, Maryland, Maine, Vermont,  and Virginia), and we can safely plan on a steady growth of customers in all of these states. (These are the only states where we intend to do business.) Customers who purchase a Snookie Notebook Computer receive a four year in-home service warranty, where one of our contracted local service providers in their state will visit the home/office of a Snookie Notebook owner and perform the required service. We will not sell a notebook computer to a resident of a state where we do not have an in-home service provider, and our only service providers are in the states where we advertise  (New Jersey plus the aforementioned states).

        I provide each customer with a line of credit - the minimum credit I provide is $150, and the maximum credit is $2,000. When my data entry assistant enters a new customer into my database, I would like to be absolutely certain that he/she will always enter the customer's name and id and address, city and state immediately, while some of the other information could be delayed for subsequent data entry.

        For each style of notebook computer we offer, we have a 4 character code, of the form AANN, where A is an alphabetic character, and N is numeric.  The display size of the notebook we build will be either vary from 13.7 up to our gigantic and beautiful 17.5 on our soon-to-be introduced Ecstasy  model (these are the diagonal measurements, in inches, of the screen size). The display types are either XGA, SuperXGA (SXGA) or UltraSGA (UXGA).   All of our notebooks offer both 56K Modems and 10/100 NICS, so we do not list these attributes in the database. 

      All Snookie notebooks will provide at least 128 megabytes of RAM, with a max of 1536 meg RAM (on our soon-to-be introduced Ecstasy  model), and these are dictated by our custom motherboard. All of the Snookie notebooks have a built in CD ROM drive, with speeds up to the maximum as listed in your Notebook table, but many of our better models also offer a CDRW, meaning that they can read and write CDs. We also have DVD drives on our better models, and we are leading the industry by offering a DVD Writer on our  Snookie Theatre model. Each notebook model is stored in a specific warehouse, numbered 4 to 8. All of our better models use NVIDEA graphics cards, but for our entry level models, we use integrated graphics.

     We currently categorize notebooks as being in one of 3 classes: either a  subnotebook (SN), a regular notebook (RN), or our exquisite deluxe MultiMedia notebooks (MM), where each class has a different form factor, internal and external design. We show our Salesreps the "Break Even Price" and our sales reps usually sell a notebook to a customer at some mark up based upon the break even price, usually from $100 to $400, and our sales reps generally negotiate this with our customers, offering some discounts to college students, senior citizens, or volume purchases.

     Our Salesreps are the key to our E-Commerce organization. We allow our Salesreps to telecommute from their home, but we insist that their home be in our home state of New Jersey (NJ).

     I started off the database design by having a consultant define several entity sets and attributes, and populating the database with some preliminary data. We have some changes since. Due to competitive pressures, with each notebook sold we preinstall (purchaser's choice) either Microsoft Office, Lotus SmartSuite,  Corel WordPerfect Suite or Sun StarOffice. So, please add an appropriate attribute named SUITE whose only possible values will be MSOFT (the default), LOTUS, COREL or SUN. {Please update the existing data to reflect the fact that all orders have been for the Microsoft Office product, except for order # 0300006, where Ms. Esther Hofstadt chose Sun StarOffice for her  Snookie Cookie and Lotus Smart Suite for each of her Snookie Tookies.)

    I would like to add two additional attributes to Notebooks - the first is  entitled "weight" which will give the weight of our notebooks. Our Snookie ThinSlice model has amazed the notebook computer industry, as we have designed it with superlightweight components, and it weighs (with battery) only 3.18 lbs. Our Theatre and Electrify models, due to the larger screens, weigh in at 7.18 lbs., while our Subnotebooks weigh 4.4 lbs, and all others weigh in at 6.54 lbs. (Our research design team tells us that we will soon have a notebook which will weigh in at 3.00 pounds, but we can never go lower than this weight.) The second is entitled "USB2" and gives the number of USB2 ports on our notebooks.  All of our notebooks offer at least two USB2 ports, but our Electrify model offers 4 USB2 ports and our Theatre models  offer 8 USB2 ports.

    Lastly, just yesterday we hired two terrific sales representatives who recently graduated from college. The first is Ms. Jennie Falkman,  and you will have to leave her address blank (she was residing at a college dormitory, but is now looking for an apartment near our main office). Her Sales Rep Number is to be 12, and she will have our new hire commission rate of 3.0%. Jennie obviously has no customers yet, but we are highly optimistic about her prospects. Our second new hire is Ms. Janet Cone, whose sales rep number will be 14, and her address is 36 Elizabeth Road, and she resides in Elizabeth, NJ - Janet will also have the same same new hire commission rate of 3.0% and, of course, has no customers yet.

    I need your further help. I am going to have my administrative assistant handle all data entry into the database, but I want to build into the database safeguards to insure that all future data entered into the database is correct.

YOUR ASSIGNMENT

     First, implement ALL the requested changes specified by the CEO.

     Based upon the above information, and beginning with the initial design view of the Snookie Notebook Computer database, design whatever you can do to insure the integrity of the database, and to make the data entry as easy as possible. (So, you will need to implement "validity checks" (via "field properties" in MS Access) on the data being entered.)

     You may download the preliminary design of the Snookie Notebook Computer database (a zip-compressed file containing SNOOKIE2004.MDB) by clicking on the monarch butterfly which will then bring you to Dr. Scher's CIS431 Database Resource Set where you should then download Snookie.zip  .

     You are free to modify anything in the existing design in order to accommodate CEO Joseph Nanna's concerns for data integrity of input data. Of course, you will need to complete numerous "field properties" for the attributes. . This assignment is open-ended in the sense that we are not explicitly enumerating all the things which should be done to insure data integrity, but obviously you should be checking things like a correct type of data is being entered, credits are within the specified limits, appropriate state codes are being used, etc. Your grade for this assignment will depend significantly on how much you do to accommodate the CEO's request for data integrity.

     Below are, minimally, a few crucial things to be designed and implemented for the CEO to ensure data integrity (and which you must document in your submission, via appropriate snapshots/screen captures), but you will probably think of more:

i) When monetary bounds are set by the CEO, we need to be certain that appropriate values are used as input.

ii) We need to be certain that all states of residence which are input must be appropriate states with the appropriate two character state code.

iii) Attributes of the notebook computers (e.g., the amount of RAM) must conform to the specifications and limits.

iv) Use input masking, required fields, etc., to insure integrity.

    Also, the CEO would also like to see testing of the validation rules for legal/illegal values. So, for at least 4 or 5 of your legal value validation rules, test your data integrity by going into datasheet view and entering some "illegal" data in a record, causing your "validation text" to appear, and do a screen capture of this (your screen capture should clearly show your illegal data in the attribute and the resulting validation text - if necessary, you might have to "move" the validation text window if it covers your illegal data.) Include within your assignment the screen captures demonstrating the success of these validation rules.

     For this assignment, you are to submit, via a screen capture/snapshot. for each modified attribute in each table, the Design View and the Field Properties for each modified attribute. DO NOT CHANGE THE ORDER OF THE ATTRIBUTES. Be sure to  provide snapshots to demonstrate that you have enforced referential integrity.  For screen captures, you may use either "Otto PrintScreen" or "ScreenSeize" (which may be downloaded from the aforementioned Database Resources page by clicking on the monarch butterfly above, or, alternatively, click on the wagging tail dog below), or you may use any other screen capture utility if you already own one. <To save paper, you might wish to have several screen captures on the same page.>   Neatly handwrite (in pen) on each page a description of  each item (screen capture, etc.). Also submit the 'datasheet' view of any table (such as the Notebooks table) whose values have been modified in any way, showing all attribute values.

    If you are not familiar with the functionality of screen capture utilities, you might wish to click on the dog with the wagging tail below to view a web page with a brief discussion of screen capture utilities:

NOTE: This is an important assignment, and a demanding and challenging one. You must begin this right away! DO NOT PROCRASTINATE. If you procrastinate and leave the work for the last  day before it is due, you will very likely not complete the assignment, and will not derive the anticipated benefit of doing this database design work using a DBMS.