CVEV 118/698 Databases

1. CVEV 118/698Databases Lecture 2 Prof. Mounir Mabsout Elsa Sulukdjian Walid El Asmar

2. Introduction • All relational database development software are similar in their way of organizing and processing information. • MS Access is a good example of a high-level DBMS. • You can develop a full-fledged DB application using Access alone, w/o the use of any other software development tool. • However more powerful DB projects can be developed by connecting to VB, VC++ etc... Applications.

3. Opening a MS Access Project • This is the start-up form. • When you create a new project, you will first be asked to allocate for it a space in the memory.

4. Database Window • The database window shows all various components of a project, of which the most important are: tables, queries, forms and reports. From it, you can create and reach those elements.

5. Tables • Tables are the fundamental data containers in a database. They are reached from the 1st tab of the database window. • Three ways are provided to create a table: • Design view: will require you to create and model the fields of the table first, then work with the data.  • Wizard: will step you through the creation of a table.  • Entering Data: will directly give you a blank datasheet, with unlabelled columns, where to fill the data. As the table is Saved, fields are given generic names (I.e. “Field1”, “Field2”, etc.) that you can later edit.  • Follow Smiley : work in design view! This will help you get used to many functionalities.

6. Table Design View Field builder Design/ datasheet view toggle Insert, Delete rows Primary key marker New object Primary key Indexes Properties Db window

7. Field Modeling • Upon creation of a field, you will/can model the following main characteristics: • Field name: max of 64 characters in length (may include spaces). • Data type: Text, Number, AutoNumber, Date/Time, etc. • Description: optional description of the field for user memo. • Field Properties: set from the bottom pane of the Design View window. See below.

8. Data Type • There are two main benefits in specifying data types in a project: • Access will prevent from putting wrong data into a field. • Storage space will be used in a more efficient way. • Main Data Types: • Text: Default type, allows a max of 255 characters per record. • Number: Modeled with Field Size property (byte, integer, etc.). • AutoNumber: Automatically assigns a unique integer to the record upon creation; commonly used for primary keys. • Memo: Text type that stores up to 64,000 characters. • Date/Time: Several formats available. • OLE Object: An ‘Object Linking and Embedding’ object is a sound, picture, or other object such as a Word document or Excel spreadsheet to embed or link to the database project.

9. Data Format • Format conforms field data to the same format when it is entered into the datasheet. • Below, NumberFormat conventions:

10. Data Format (Cont’d) • Below, Text and MemoFormat conventions:

11. Controlling Data Entry • Input Masks: control value of a record and set it in a specific format. They are similar to the Format property, but instead display the format on the datasheet before the data is entered. • Example: In a telephone number field TELNUM (data type Text or Number), the following input mask is specified: (999) 888-7777 Thus, upon entering this attribute in a record, the blank field would look like: (___) ___-____

12. Input Mask Symbols

13. Controlling Data Entry (Cont’d) • Indexes: allows Access to query and sort records faster. Set a field that is commonly searched and change the Indexed property to Yes (Duplicates OK) if multiple entries of the same data value are allowed or Yes (No Duplicates) to prevent duplicates. • Field Validation Rules: specify requirements (change word) for data entered. A customized message can be displayed when data violating the rule setting is entered. Examples: <> 0 to not allow zero values in the record, and ??? to only all data strings three characters in length.

14. Queries • Queries are usually developed for questions that are regularly asked about a data. • The answer to a query is specifically displayed as an answer table, of which the collection of records is called a dynaset (short for Dynamic Subset). • Yet, we distinguish several types of queries: • Select: Extract data from tables based on specified values. • Parameter: user specifies criteria on which to query. • Range: Select fields/records which contain a range of values. • Group By and Crosstab: displays summarized values in a grid form taking its rows and columns from chosen fields. • Action: change records in a table (Delete, Update and Append queries) or create new tables (Make-Table query).

15. Creating Queries in Design View • Show Table: Choose table(s) upon which the query will be built. • Query Design Window: • Table/Query Pane: displays table(s) of concern. • Query Design Pane: grid of definition.

16. Creating Queries in Design View (cont’d) • Before developing a query, you can specify its type in design view by clicking on the Query Type button on the toolbar. • SQL: Structured Query Language was specifically developed to build complex queries. • It is compiled to elementary relational algebra operations (like intersection, union, etc...), which makes it very efficient. • However it is seldom written in detail by the user, for most manipulations can be done on the query design pane.

17. SQL • Generic syntax: SELECT <field list> FROM <table list> [INNER JOIN ...] [WHERE <criteria>] [ORDER BY <field list>] ... • The WHERE clause can contain all the logical operators you know, and more, like AND, OR, IN, LIKE, etc… • The ORDER BY clause allows sorting of the data by fields (columns). • The INNER JOIN clause allows union of two tables with a common column.

18. Forms • Access Forms are similar to VB forms, but different in objectives: the main purpose of a form in Access is to manipulate data in and out of the database.

19. Forms (Cont’d) • Most of the properties of the forms and controls are data-aware, and are designed specifically to display fields or tabular data. • Data Binding is performed simply by setting the Record Source property of the form to the appropriate Table or Query. • Each control can be data-bound by setting its Control Source property to the field it corresponds to. • It is possible to include a linked sub form in a form, which is suitable for certain cases, and simplifies the process of data entry.

20. What’s Next • Your new best friend 