Learning Outcomes:

• Be able to appreciate the key role of databases in an organisation's Information System
• Be able to demonstrate an understanding of the relationship between data and information
• Be able to relate the methods for getting data into and out of a database system
• Be able to distinguish between File Systems and Databases
• Be able to relate the advantages of database systems over file systems
• Be able to distinguish different data models
• Be able to understand the relationship between tables, records, fields and data in a relational database
• Be able to relate the nature of a "relationship" within a relational database
• Be able to state the purpose of a query in a database system
• Be able to create and manipulate a simple database created in MS Access
• Be able to identify the entities and their relationships within an enterprise

Computer Lab Exercises

Week 8

You are about to start designing a database system from which will run your project business. In this week's exercises you will identify the "entities" that exist in your project business. Entities are people, roles, objects and things that have a definable relationship with other entities within the business. If it helps think of what each of the people that interact in and with your business do and the people and objects that they interact with. This is very like the "Entity-Relationship" diagrams that we have seen in lectures/workshops and is the key to starting the database design for your project.

1. Create a word document called "entities.doc". At the top place a large heading that says: "Entities" and below that a smaller heading that states the type of business that your project is about. Be as creative and as complete as you can with the tasks below as they will be used to design your business' database system.
2. Create a comprehensive dot-point list of the roles of people that interact with and within your project business. If you have a restaurant for example, there would be "customers", "waiters", "chefs", "suppliers" and several others. If it was a hospital there would be: "patients", "doctors", "nurses", "suppliers" and others again.
3. For each role create a nested sublist that lists the identifying aspects of that role. Some items will be singular like "name" or "address" and some things will be a list like: "medical history" or "qualifications". For example a supplier might be identified by: "company", "supplier code", "contact name", "contact number", "product list", etc. In a marketing business a salesman might be identified by: "first name", "family name", "employee number", "mobile phone number", "list of successful sales", etc. Try to imagine the types of information that if kept at hand might help your business run more efficiently or effectively.

NB: The next step - When you have come up with a list of roles like "nurse", "administrator", "secretary" and that sort of thing it is then time to think whether these can be grouped under a more general heading like "employee" or "staff". This is called factorising.

If you look at the identifying aspects they are probably the same for each entity type meaning that if we create separate tables in our database there will be several with exactly the same types of information . We dont want repetition so create a more general role definition, keep the same identifying information with a key value like "Employee number" but add an attribute like: "role" or "job" which would hold the job title.

For external entities there will be certain groups which will be obvious like "suppliers" and "consultants" which for many businesses would have several names. A separate list for each of these groups is fine. Some external entities will seem to have only one member like: "Government contact" or "Phone company". How about grouping these types of entity under a heading "Business contacts"? You would keep things like: "contact code", "contact name", "address", "contact number" and maybe have an identifying attribute like: "Company".

These will reduce the number of different files or tables that you have to keep track of.

4. Create second dot-point list that itemises all of the types of object that your project business uses or interacts with. These might be products that are created, documents or consumable items that are used in the course of doing business or less frequently used items like books or paintings or furniture. For example, a robotics firm might use: "robots", "computers", "furniture", "software", "hardware", "stationery", and several other things.
5. For each type of item/object create a nested sublist that uniquely identifies it. What kinds of records would you want to keep? As an example, furniture might be identified by: "description", "serial number", "brand", "supplier code", "unit price", etc. Vegetables used in a restaurant might be identified by: "name", "supplier", "product code", "current stock", "reorder level" and any other identifying aspect.

NB: The next step - As with the people type entities are there more general groupings of the object entity items that you have identified? Could "nuts", "bolts", "washers" and "robot arms" come under a heading like: "Parts"? Could things like "steak", "fish", "potatoes" and "peas" come under a heading like: "Groceries" or something similar?

6. Reflect. Make an entry in your Blog that outlines the new knowledge and skills that you have gained from this week's lessons. Hint - Answer these questions:
   • What things didn't you know before?
   • What things did you understand differently before?
   • What things had you never done before?

Remember to “back up”.  Have you got a copy of all of the files that you created on both your USB and network drives?

Week 9

If you have completed last week's tasks you will have generated some general classifications of "entity" that exist within and interact with your project organisation. To give you a bit of a clue most projects might have the following entities that you would want to keep records about:

Psych Ward

• Patients
• Employees
• Consultants
• Suppliers
• Assets
• Drugs

Property Marketing Company

• Properties
• Developers/Owners
• Clients
• Employees
• Assets

Robotics Development Company

• Clients
• Employees
• Subcontractors
• Suppliers
• Inventory (parts, products, etc)
• Assets

Seafood Restaurant

• Suppliers
• Employees
• Customers (special functions)
• Supplies
• Recipes
• Assets

The lists above are only an idea and may be a bit different in your project. I am sure that you get the idea.

Now we are going to start designing a database using your basic three entities. In most cases this will be Employees, Suppliers and Supplies or something similar. It is important to note that there are relationships between these basic entities. Obviously supplies have to come from a supplier so each supply or inventory item has a supplier code associated with it. Suppliers are usually contacted by a particular employee and have a working relationship so each supplier record might have a contact employee code.

1. Data Design: For each entity in your project organisation you created an identifying attribute list. At the end of each attribute place the data type and size in brackets. Eg:
   • Employee code - (Alphanumeric, 15 characters)
   • Name - (Alphabetic, 20 characters)
   • Address - (Alphanumeric, 50 characters)
   • Postcode - (Numeric, Integer, 4 digits) or (Alphanumeric, 4 characters)
   • DOB - (Date)
   • Expiry - (Date)
   • Price - (Currency)
   • Stock level - (Numeric, Integer, 3 digits)
   • Reorder point - (Numeric, Integer, 3 digits)
   • Telephone number - (Alphanumeric, 10 characters)

   In general, a number that you are going to perform some mathematical calculation with should be stored as an Integer (eg, 1, 34, 256) or as a Real number (eg, 1.23, 54.565) as appropriate. Any other numeric field that is not used for calculations can be stored as alphanumeric "text" characters.

2. Validation: Identify any attributes that might need checking to ensure that they are correctly entered into the database. For example numbers might have to be within a certain range or there may only be three valid codes or identifiers or it may be a numeric field only.
3. Supplier table: Each project will have a supplier or developer or contractor list that holds names, business addresses and contact numbers in a table. We are going to create a table in Microsoft Access with the following attributes:

   Field Name	Type	Size	Description
   Supplier Code	text	8	Unique identifier for suppliers
   Supplier Name	text	30	Company name
   Address	text	30	Street address
   Town	text	20	City town or suburb name
   Postcode	text	4	Postal or zip code
   State	text	3	State (abbreviation)
   Phone Number	text	10	Contact phone number for orders
   Contact Name	text	20	Name of sales consultant
   Order clerk id #	text	8	Employee id# of our ordering clerk

   1. Creating a Table:Use MS Access to create a new blank database on your flash drive or student network drive. Save the new database as "Week9.mdb". You will have a a menu system that will have database objects on the left and relevant activities on the right. There will be a table named Table1 in the left hand pane. Right click the Table1 object and select "Design view". (you can also right click the Table1 tab in the right hand pane to select the view) At the prompt, name the table "Suppliers". Something a bit like the table above will appear ready to start entering data descriptions.
      
   2. Fill in the Field names, Data type and Description (Yes you must type it in) as per the table above - feel free to adjust it to your particular requirements. You will notice that the default data type for the first field is Autonumber. When you select the Data Type field a drop down list becomes available. Select Text from this list. Subsequent new fields should default to data type "Text".
      
   3. For each field click on the data type entry, select Text if necessary and look down to the "field properties" view below in the "general" tab. The default field size is 255 characters. You can click on this value and change it as required in table above.
      
   4. For the Supplier Code there must be a unique identifier that every supplier in the database must have. There should be no other records in the table with the same code. To ensure this set the "Required" field to "Yes", "Allow zero length" to "No" and "Indexed" to Yes (No duplicates)
   5. The Supplier Name should have similar attributes so that each supplier has a name entered into the database but there could be companies that might have the same name or abbreviation. To allow this the "Indexed" field should be Yes (Duplicates OK)
   6. Every table has one or more "key" values that are used as unique identifiers. We could use just the Supplier Code but to ensure uniqueness we will set both the Supplier Code and Supplier Name to be key values. To do this go to the gray area to the left of the Supplier Code field click on the gray tab and select that and the Supplier Name field below (they should both be highlighted). Go to the menu bar and click on the button that resembles a small key. A small key symbol should appear in both of the gray tabs to the left of the field names.
   7. Use the save utility to save the table that you are working on as "Suppliers" (or whatever is appropriate to your requirements). Close the table design view. The next step will save you the effort of filling out the table.
   8. Importing Data: Download the file "Suppliers.csv" to your flash drive or student drive. In MS Access go to the menu bar and select the External Data Tab, select Import-->Text File. Navigate to where you saved the Suppliers.csv file. Select "Suppliers.csv".  Choose "Append a copy of the records to the table" and select Suppliers from the drop down list. A dialogue box/wizard will appear that will lead you through the importing of the supplier data into your Supplier table. The important points are that the csv file is comma delimited and there are no field names in the first row.
   9. Open up the Suppliers table and look at it in the "Datasheet" view. All of the field should be filled in with 15 different suppliers.
   10. Close the Suppliers table and start designing new tables for each of the other basic entities that you have identified. In most cases this will be a Product, Inventory or Supplies table and an Employee table. Your Product table should have fields that include a unique product code, name, description, quantity and a supplier code. Remember to consider data design and validation. Populate the Product table with 5 different items (use your imagination) and then save it.

   Remember to “back up”.  Have you got a copy of all of the files that you created on both your USB and network drives?

   1. It is important that you don't use the "Save As" facility in MS Access to try and create copies of the database. This does not work the same as for the other Microsoft applications. After you have saved your work and closed down MS Access you can use Windows Explorer to find the .mdb file, Select, Copy and then Paste it to the directory that you want to back up to

Week 10

You should have at least 3 tables designed and partially populated with sample data before you start this stage. For the basic three tables; suppliers, product and employees there should be some common elements. Each product in the product table should have a field that contains a supplier id number. The supplier id is the unique code that you entered as the first element of you supplier table. In this way we have a link oro shared value between the tables. Similarly the supplier table has an entry that is the employee contact id that identifies the employee that usually places the orders with that supplier. Other links between tables might be realised if a recipe table links to the product table through product codes or a patient's record links to a drug id with several other possibilities.

Make sure that your product table has fields that show the current stock of the item and the reorder point of that item.

1. Use the wizard feature to create a data entry form for each of the tables that you have designed
2. Have at least 3 employees that share the task or ordering from at least 10 suppliers. Make sure that their employee code is entered into the supplier table in the Clerk id field
3. Have at least two suppliers that supply a range of more than 3 products
4. Use the relationships feature to create a relationship between Supplier and Employee tables through the Employee Code
5. Use the relationships feature to create relationship between Product and Supplier tables through the Supplier Code
6. Create a query that returns a list of products that one of your popular suppliers provides
7. Create a query that returns all of the products that should be reordered (stock <= reorder point), who the supplier is, the contact name, contact phone number, contact employee first name
8. Create a query that returns a list of Employee numbers and first names, the product names that they are responsible for ordering and the relevant supplier name, supplier contact and telephone number
9. Create a report that lists the current inventory of stock
10. Create a report that lists the items that need reordering
11. Link the database that you have created to your website
12. Reflect. Make an entry in your Blog that outlines the new knowledge and skills that you have gained from this week's lessons.

Focus: What have I learned?

Reflect. - Think about what you have done above and answer these questions:

1. What do you think you have learned from this module and its exercises?
2. Where do you think this knowledge will be useful?
3. What did you find interesting about the exercises?
4. Why were they interesting?
5. What did you find uninteresting about these exercises?
6. Why were they uninteresting?
7. How many hours per week have you spent doing work for this course in the last 2 weeks?

Remember to “back up”.  Have you got a copy of all of the files that you created on both your USB and network drives?

NB: Save your finished database with tables, queries, forms and reports on your website.

---

Resources List:

• Shelly, B.G., Cashman, T.J., Vermaat,M.E., "Discovering Computers: A Gateway To Information 2006", Chapters 3,10,14, Thomson Course Technology, 2006
• PowerPoint Slides - Introduction to Databases and Database Management
• Shelly, B.G., Cashman, T.J., Rosenblatt, H.J., "Systems Analysis And Design: Sixth Edition", Chapter 7, Thomson Course Technology, 2006
• PowerPoint Slides - Information Design
• Florida Gulf Coast University - Online tutorials (Word, Access, Excel, etc)

---

 

Updated by Heather Gray Feb 2011