INLS102 Week 12: Spreadsheet Functions Database Concepts

1. INLS102 Week 12: Spreadsheet FunctionsDatabase Concepts Date: 11/15/05 Instructor: Leo Cao SILS, UNC-Chapel Hill

3. Excel Spreadsheet application by MS Fairly simple to use, got good depth of functions if you explore it One of the �killer apps� for business to adopt computing

4. Excel cont�d The one key thing to remember about excel is that every cell is an object This allows you to manipulate the elements in the sheet (Cell_A + Cell_B) / Cell_C = result That formula above, in various forms, speaks for the versatility of an spreadsheet app In a sense, you have a functional document Spreadsheets can perform complex statistical analysis as well, I�ll leave that to your leisure pursuits Often good for text as well If you get a massive batch of text, try importing into excel, use the eliminators, often times it�ll save you a load of time

5. What is a database? An organized and structured collection of data, and relationships between data. Types of databases Flat files Simple list style storage, direct input/output/edit, such as excel, manageable when data is limited Relational databases Using tables with fields to store data Establish relationships between tables to effectively manage data The excel example. The excel example.

6. Picturing a database?

7. Database functions Database examples? Functions of database Data storage Data management Data retrieval Why Use relational databases? Reduce data redundancy Ease of backup (save another copy) Performing complex queries Managing multi-user access Onyen, other examples. Onyen, other examples.

8. Database structure Table Records � �A database file is a group of records that are identical in structure. A record contains information about a single item in your database.� Fields � �Each record is made up of a series of fields which store individual bits of information inside a record.� Relationships � How each entity relate to each other

9. Table structure Fields Primary key Unique to each record, composed of 1 or a set of attributes (id #, or name + phone #) Foreign key The field being connected to by the primary key in the other table Primary and foreign keys are the same attribute, just in different tables, they must have matching data, that�s how referential integrity is enforced.

10. Table structure Field type --- data type character: alphabetic (name), numeric (age), alpha-numeric (address) number: numeric information to be used in calculations (price) dates & time: mm/dd/yy or dd-mm-yy yes/no: true/false, male/female, etc. memo: free text of variable length, comments, description "NULL": empty Field length --- keep to the possible maximal length

11. Entity Relations ER diagrams are used to abstractly represent real world entities in systems terms An essential concept in databases The first entity is always the primary entity

12. Types of relationships One-To-Many: diagram http://ils.unc.edu/inls102/Fall2004/notes/w11_db/one-to-many.jpg the most common type of relationship a record in the primary table corresponds to more than one record in the related table i.e.) Customer-to-Order: customer_id One-To-One: diagram http://ils.unc.edu/inls102/Fall2004/notes/w11_db/one-to-one.jpg matching fields on both sides are primary keys i.e.) AuthorName-to-AuthorDates: author_id Many-To-Many: diagram http://ils.unc.edu/inls102/Fall2004/notes/w11_db/many-to-many.jpg more realistic relationship i.e.) Name-to-Address: name_id to address_id represented as two one-to-many relationships through a junction table junction table contains pointers to the primary keys of each table

13. Database: Access Intro One of most popular relational database software around Can be a bit frustrating to learn, but not too difficult Very frequent and annoying error msg pop-ups, just don�t panic, read it and make corrections, it�s mostly there to forcibly prevent errors by the user

14. Intermission � 15sec pause Any questions on what we just went over? Probably not enough time to go through the exercise today ?

15. Access exercise Read and complete the following, 1. Create a new database file (File --- new), save the file as Shopping.mdb 2. Create Customer Table 1) 6 fields: customer_id, last_name, first_name, phone, birthday, children 2) make customer_id as the primary key (right-click the field, and select primary key) 3) make last_name and first_name as required fields (in the general tab at the bottom, �required� --- choose �yes�) 4) set data type � Number: customer_id � text: last_name (field size:20), first_name (20), phone (15) � date/time: birthday (format: short date) � number: children (field size: Byte) 5) set input mask � phone: (xxx) xxx-xxxx --- click on the ��� next to input mask to select 6) add 5 records 7) save the table as Customer 3. Create the Product table 1) 4 fields: product_id, product_name, company, price 2) data type � product_id: AutoNumber � product_name, company: Text � price: Currency (decimal places: 2) 3) Set product_id as a primary key 4) Type four records in the table (change to datasheet view) 5) Save the table as 'Product'

16. Access exercise cont�d 4. Create the Order table 1) 5 fields: order_id, customer_id, product_id, date, time 2) set order_id as a primary key 3) data type � order_id: AutoNumber � customer_id, product_id (related fields): Number/Long Integer � date: Date/Time ('Short Date' format, in the general tab at the bottom) � time: Date/Time ('Medium Time' format, in the general tab at the bottom) 4) enter 5 records 5) save the table as �Orders� 5. Generate the relationships a. Tools --- Relationships b. Select all three tables to show c. Drag the customer_id in the Customer table to the customer_id in the Order_table d. Drag the product_id in the Product table to the product_id in the Order_table. Remember to check the �referential intergrity� on the pop-up window. http://www.unc.edu/~lcao/inls102test/shopping.mdb (full db)

17. For Thursday Search engine assignment Make it available on your webpage, email to notify me