DATABASE DESIGN and CREATING DATABASE: DATABASE DESIGN and CREATING DATABASE WELCOME Click Here DATABASE DESIGN : DATABASE DESIGN Click to Start : A Short Case Study EZConsulting Inc. is a small consulting company offering database design and creation services to a fairly wide range of customers. The company employs about 30 consultants, analysts, programmers, network specialists, who will work in teams on projects for periods of time ranging from a few days to several months. PowerPoint Presentation: At any given time there may be 10-12 different projects on the go. Because resources are scarce a specialist may be called upon to work on several projects simultaneously. In order to keep some control over scheduling and costing, every employee is assigned to a department and reports to only one manager, even when he's working on projects for other departments. Every week every employee must submit a timesheet showing the number of hours spent on each project. PowerPoint Presentation: As was the case for the shoemaker's children (they had no shoes because dad was too busy making shoes to sell in order to put bread on the table), this company has no Project Management database, simply because nobody has had the time to set one up. And this is typical in this kind of environment. Do you take an analyst who bills $800/day and put him to work on in-house maintenance? No, you don't. You wait until some bright college student shows up for a co-op work assignment and you give him/her the job. The company hopes that after the basic Project management application is operational other modules such as Employee Skills Management and control of bids and RFP's can be integrated to the database. PowerPoint Presentation: Designing the Project Management Application Here is what our first draft of the E-R diagram should look like for the Project Management case: Figure 2-1 PowerPoint Presentation: The diagram contains the information we would have gathered by talking to the client. Notice that the attributes for Employee represent the minimum amount of information we have to keep at this time. We haven't included things like "Home address", "Date of birth" and so on. When we start working with SQL later we will add more information to the table. The same applies for the other entities - we will add attributes as we develop the model later on. PowerPoint Presentation: It is important to make sure at this point that you understand the degrees of the relationships shown. Department <--> Employee is a one-to-many relationship - a given employee is assigned to one and only one department and a given department contains zero or many employees. This means that every employee in the company will be assigned to a department, even the President who will be in Administration. A department may exist and have no employees assigned to it. For example, we could create a new department in the database and, until it is staffed, it will have zero employees assigned to it. PowerPoint Presentation: Employee <--> Project is a many-to-many relationship - a given employee works on one or many projects and a given project may have zero or many employees. In order to keep track of every employee's hours, all the work that is done will be billed to a project. However, projects could be things like "In-house systems development", "Professional development leave" or "Administrative duties". Any project may have no employees working on it. Now, once you have the E-R diagram down, you go over it one more time with the client to make sure that you have the details down correctly and you are almost ready to start creating the actual database. PowerPoint Presentation: NORMALIZATION PowerPoint Presentation: It is possible to start creating the database at this point. It's just a question of creating a new table for every entity identified in the diagram. We'll be using MS-Access to do that shortly. But how do you code the relationships? There is a formal process to do that in database modeling. It's called normalization . It means applying a set of rules to the data so that you group the attributes in such a way that the relationships work. It's not really that complicated but it is a formula approach. If you prefer to use that approach, get any good book on databases, look-up "normalization" and follow the steps. PowerPoint Presentation: We'll do normalization using the intuitive approach - work with the data until it "feels" OK. This could also be called prototyping - create a working model of the database that is close to what you want and keep improving it until it works perfectly, then put it into production. However, whatever the approach taken, there are some basic rules that have to be adhered to. The rules apply to any relational database and cannot be broken . They can't even be stretched. PowerPoint Presentation: Think of them as the Prime directives . The rules are: Every table must have a primary key - an attribute or combination of attributes that uniquely identifies every occurrence in the table. The primary key can never contain an empty or Null value. That makes sense - if you had 2 that were empty, they wouldn't be unique anymore. Every attribute of every occurrence in the table can contain only one value. Think of the Employee table as a grid. Every occurrence, or line, represents one employee and every column is an attribute. So, every employee can only have one ID and one First-name and one Last-name, and so on. PowerPoint Presentation: ONE TO MANY RELATIONSHIP PowerPoint Presentation: Let's start with the easiest relationship: Employee <--> Department. First we create a new database and call it ProjMgt.mdb . Then we create the first two tables in the database: call them Employee and Department , and put in the fields from the E-R diagram. Notice that the column-names in the tables are all coded with a prefix: e_ for Employee, d_ for Department and p_ for Project. This is a good habit to get into. It will make your life easier later on. This is what we now have: PowerPoint Presentation: FIGURE 2-2 PowerPoint Presentation: 2 ways it could be done: 1.Add a column for Employee ID to the Department table. You get this: FIGURE 2-3 PowerPoint Presentation: See the problem? When you start entering data, what do you put into the d_Employee column? Rule 3 says you can have only one value. What if there are 2 employees in the Departmen d_Second_employee t ? You could try to add another column for, but what if there are 20 employees, or 200? Obviously this is not going to work. So we scrap this brilliant idea. PowerPoint Presentation: 2. Add a column for Department to the Employee table. You get this: Any problem with this? Doesn't seem to be. Since every employee is assigned to only one department, I only have one value to put into the column: employee 101 works for department 10, and that's all. PowerPoint Presentation: In summary, to normalize a one-to-many relationship you add a column to the table at the "many" end of the relationship to refer to the primary key at the "one" end. PowerPoint Presentation: MANY TO MANY RELATIONSHIP PowerPoint Presentation: The many-to-many Employee <--> Project relationship is a bit trickier. In the end we want to associate projects and employees, to see who is working on what project. To see how it must not be done we'll go through the exercise of adding columns to the tables. So we add the Project table to the relationships: PowerPoint Presentation: To create the relationship we could add a Project_Number column to the Employee table . When we try it we see that we come up with the same problem we had in the previous relationship: when we get to the e_Project column, what do we write? The employee could be working on 7 different projects. Rule 3 says we can only enter one value. PowerPoint Presentation: So we try it the other way - add an Employee_Number column to the Project table . Again, when we get to the p_Employee column what do we write? There could be 25 employees working on this project. PowerPoint Presentation: Since those two attempts obviously won't work, there has to be something else. It's called a link entity or link table . Most textbooks will just call that table Employee-Project or Project-Employee . But in real life the entity does exist in our system. What is it that links employees with projects? Right! It's the timesheet. The timesheet contains all the information we need. PowerPoint Presentation: So we add the Timesheet entity to the mix and modify our E-R diagram: PowerPoint Presentation: t_Employee is an employee-ID that refers to the Employee table, t_Project is a project_number that refers to the Project table , t_Date is the period_ending date for the timesheet t_Hours is the number of hours the employee spent on that project . We also specify that every line in the Timesheet table must have one and only one employee_ID and must have one and only one project_number . In other words we cannot create a Timesheet for an employee who doesn't exist or charge for work on a project that doesn't exist. PowerPoint Presentation: What is the primary key for Timesheet? To get a feel for the key, let's look at the data that will be input: PowerPoint Presentation: It's clear that t_Employee or t_Project can't be the primary key because they both repeat; remember: every occurrence in a primary key column must be unique. How about a concatenation of t_Employee + t_Project . That looks good so we try it. It works fine for one week. The following week, employee 202 has worked on project S4440 again and we get a duplicate key error! PowerPoint Presentation: So we add t_Date to the key and that solves the problem. Now, assuming that the client has said that if an employee works on a project twice in one week he adds-up the hours, the combination of employee + project + date is truly unique. Conclusion: there is only one way to normalize a many-to-many relationship and that is to create a link table. The link table must contain columns that refer back to the other tables so that the many-to-many relationship becomes two one-to-many relationships. CREATING DATABASE: CREATING DATABASE Continue PowerPoint Presentation: THE PROJECT MANAGEMENT EXAMPLE PowerPoint Presentation: EZConsulting Inc. is a company doing IT systems consulting work with a large number of clients. At any given time there are dozens of projects on the go, each employing several employees. In a given period (in this case, weekly) an employee could work on several different projects. In order to track costs and revenues for each project, each employee will submit a timesheet every week showing the number of hours spent on each project. And, since all employees are attached to only one department, costs and revenues can be calculated by department. PowerPoint Presentation: It has already been determined that the ProjectMgt database will consist of the following tables: Employees : details on every employee - ID, name, address, telephone, date hired, salary, chargeout rate, department Projects : details of every project - project number, title, budget, start date, end date Departments : lookup table of departments - number, name, head Timesheets (Master/Detail) : tables to store time spent on projects - date, employee, project, number of hours PowerPoint Presentation: The first task to be developed in the application consists of table maintenance . For each of the main tables, Employees, Projects and Departments, there have to be ways to add new records, remove records that are no longer needed and change records when appropriate. For example, new employees are hired and other employees leave, the name of a department is changed or a new project is started. Each of these maintenance operations will require a separate form. PowerPoint Presentation: Once the maintenance functions are in place, and they have to be (remember: referential integrity dictates that you can't accept a timesheet for a non- existant employee or non- existant project), we can start working on the operational functions , entering timesheets and producing reports. There will be forms for these tasks also. To make it easier to access the different forms, we will create an Application Menu like we did in the previous lesson. The layout of the Menu form is standard and the code consists of a series of Load and Show statements for the various forms. PowerPoint Presentation: PowerPoint Presentation:
Version problems VB 6 and Access 2000 have compatibility problems. Because VB 6 was released before Access 2000, it does not normally recognize the Access 2000 format. In the example that follows, look at the Connect property of the Data control . If you don't have Access 2000 in the choices when you open "Connect", you have an older version of VB. If you try to connect to an Access 2000 database, you will get a message saying that you have an " Unrecognized database format" . If you have an older version of VB6, you will have to get the fix for it. You may be aware that Microsoft regularly publish upgrades to their software products (not that they admit that there are problems with them!). Those upgrades are called Service Packs . Right now, Visual Studio (which includes Visual Basic) is at Service Pack 5. By the time you read this that may have changed. So, to fix your compatibility problem you will have to download the latest Visual Studio Service Pack from Microsoft . There is a quick fix to the problem, which is what we've done here to save you the trouble of having to download. You can convert your Access 2000 database to Access 97 and use your old VB. To do that in Access 2000, go to Tools -->Database utilities -->Convert and that will do the trick until you have the time to upgrade VB. This will also come in handy later when we look at a VB
called Visual Data Manager . Unfortunately, that Add-in does not work at all with Access 2000, even with the VB Service Pack. If you want to use it you will have to convert the database.
PowerPoint Presentation: DATA CONTROL PowerPoint Presentation: To begin the application, we will first create a new form for Projects maintenance: ProjMaint . The first control we will place on the form, once we've set the basic form properties and saved it, is called the Data Control . It is the object which links a form to a database and allows you to access the fields in the tables making up the database. It's called Data in the Toolbox. PowerPoint Presentation: VB provides other objects that allow you to link to databases. ADO (ActiveX Data Objects) are certainly more powerful and more efficient than the Data Control. However, they do require a lot more coding and are more difficult to implement. Also, they are not available in the Standard Edition of VB, only in the Professional and Enterprise Editions. In simple applications, the Data Control, slow as it is, gives you a tool that is easy to implement and will provide most of the functionality you need. PowerPoint Presentation: PowerPoint Presentation: The arrow buttons on the control are used to navigate through the database records: First record and Previous Next and Last record The buttons correspond to 4 methods of the DC which you can use when you have to navigate using code. They are: MoveFirst MovePrevious MoveNext MoveLast PowerPoint Presentation: The Important Properties of the Data Control: Name : the name to use in code - Data1 is default - eventually we'll have several data controls on the form - we'll call this one dta_proj . Connect: the kind of database - in this case it's Access - could be Foxpro , dBaseIV , etc. DatabaseName : the name and path of the database the control is connected to. PowerPoint Presentation: RecordSource : the name of the database table being used. BOFAction and EOFAction : action to take when trying to read before the beginning of file or past the end of file - we'll look at those later. Recordset : this is a run time property, and it's an important one - it represents the result of the query executed to the database - it contains all the records required by this Data Control - when you navigate through the database, you are actually navigating through the recordset , which is then mapped back to the database - that is why the methods of the Data Control refer to the Recordset property. PowerPoint Presentation: Next we add the controls needed to look at the fields in the records. In many instances we will need to make changes to the data Therefore, we'll use a TextBox for each of the fields so that we can both diaplay and enter data as needed. Each TextBox will be a bound control , meaning that it is bound or tied to a specific field from the database. When we navigate through the database using the arrow buttons the content of each TextBox will always reflect the content of the current field. PowerPoint Presentation: To bind the control to the database field we use its Data properties : DataSource is the name of the Data Control - remember that the DC specifies the name of the database to use and the name of the table to access - tip: enter this one before the DataField . DataField is the name of the field to bind - that field is selected from the content of the table . PowerPoint Presentation: Notice that we've also added several buttons to the form. These buttons represent the most common actions we have to perform on the records PowerPoint Presentation: Important note : when you work with bound controls you have to remember that every time you move off a record, the record is automatically modified in the database - every change you make to a TextBox is reflected immediately in the table. That is why there is no Update button - the function is done automatically. The Reset button allows you to cancel any changes you may have made to the fields - if you modified information and then change your mind about updating it, the Reset will skip the automatic update and return the fields to their original state. This is a method of the Data Control object and is written as: dta_proj.UpdateControls PowerPoint Presentation: There are 2 ways to Add new records to the table: in the Data Control, dta_proj , set the EOFAction property = 2 - this will allow the user to go to Last Record and do a Next, which will add a blank record to the table; use the AddNew method of the Data Control, as in: dta_proj.Recordset.AddNew To Delete the current record , you must use the Delete method followed by a MoveNext to move off the deleted record: dta_proj.Recordset.Delete dta_proj.Recordset.MoveNext PowerPoint Presentation: PowerPoint Presentation: Validating Data Before the data you are entering get permanently transferred to the database, you often want to make sure they are correct. That is called data validation . We look here at two simple ways of validating data. Let's say that the specs for the Projects maintenance part of the application call for three verifications: a budget amount must be entered; the budget amount must not exceed $1,000,000 the project end-date cannot be earlier than the start-date. PowerPoint Presentation: VALIDATING DATA PowerPoint Presentation: Before the data you are entering get permanently transferred to the database, you often want to make sure they are correct. That is called data validation . We look here at two simple ways of validating data. Let's say that the specs for the Projects maintenance part of the application call for three verifications: a budget amount must be entered; the budget amount must not exceed $1,000,000 the project end-date cannot be earlier than the start-date. PowerPoint Presentation: For the first two we'll use the Validate event of the control. This event is triggered when the CausesValidation property in the TextBox is set to True. Before losing focus, the validation is done. The parameter assigned to the event when it is generated automatically (it's called Cancel) represents the KeepFocus property. If you set it to true in code when you encounter a validation problem, it keeps focus on the current control until the problem is corrected. The second technique is to use the LostFocus event . When focus moves off the control, you do the validation. If there is an error, you evoke the SetFocus method to put focus back to the control with the error. PowerPoint Presentation: FINDING SPECIFIC RECORD PowerPoint Presentation: When you navigate with the arrow buttons or the Move... methods you are necessarily moving one record at a time. Very often there is a need to access a specific record in the database. For example, it might be to change the ending-date for the project called "XYZ Corp. Payroll System". In this example we assume that the search will be conducted on Project title. It could be on Number or End-date if necessary and it would just involve minor changes to the code. We also assume that the user does not want to enter the full project title and will only input the first few characters; we will therefore make use of the "Like" operator to match the recordset to the search string. PowerPoint Presentation: First, we create a new TextBox , called txt_findTitle , to enter the search string. We will give this TextBox the TabIndex 0 because we want it to be the first control read when we look at a record. As soon as we move off the TextBox , the LostFocus event is triggered and checks whether the user has entered a search string or not. If there is no input into the search string, the user can work with the current record in the form. If there is a search string specified, the appropriate record will be loaded into the form. PowerPoint Presentation: The FindFirst method of the DC will locate the first occurence in the recordset matching the "content" parameter. If there are more than one records that match, the user then navigates forward using the arrows. The format of the FindFirst method is: DataControl.Recordset.FindFirst "fieldname = ' searchstring '" If the fieldname contains a string value, you have to use single quotes to name the searchstring ; you can use the other comparison operators in place of the =. This technique can be adapted to search any field in the recordset for a specific record. There are also FindNext , FindPrevious and FindLast methods for the Data Control recordset . PowerPoint Presentation: PowerPoint Presentation: REFERENCES: REFERENCES http://www.vbtutor.net/ http://www.profsr.com/vb/vbless09.htm ASSESSMENT: ASSESSMENT 1. The name and path of the database the control is connected to: 1. The name and path of the database the control is connected to a.) RecordName b.) RecordSource c.) DatabaseName d.) Connect 2.The kind of database - in this case it's Access - could be Foxpro, dBaseIV, etc.: 2.The kind of database - in this case it's Access - could be Foxpro , dBaseIV , etc. a.) RecordName b.) RecordSource c.) DatabaseName d.) Connect 3.This is a run time property, and it's an important one - it represents the result of the query executed to the database : 3.This is a run time property, and it's an important one - it represents the result of the query executed to the database a.) Recordset b.) RecordSource c.) DatabaseName d.) Connect 4. The control are used to navigate through the database records: 4. The control are used to navigate through the database records a.) Boxes b.) Codes c.) Letters d.) Arrow Buttons 5. It is the object which links a form to a database and allows you to access the fields in the tables making up the database: 5. It is the object which links a form to a database and allows you to access the fields in the tables making up the database a.) Data b.) Data Control c.) Files d.) Arrays 6. Easiest relationship: 6 . Easiest relationship a.) Parental b.) One-to-One c.) One-to-Many d.) Many-to-Many 7.Trickier relationship: 7.Trickier relationship a.) Parental b.) One-to-One c.) One-to-Many d.) Many-to-Many 8. The name of the field to bind - that field is selected from the content of the table. : 8. T he name of the field to bind - that field is selected from the content of the table. a .) DataCode b.) Data c.) DataSource d.) DataField 10. Code to Add new records to the table: 10. Code t o Add new records to the table a.) dta_proj.RecordsetAddNew b.) dta_projRecordset.AddNew c.) dta_proj.Recordset.AddNew d.) dta_projRecordsetAddNew 11. Delete Method: 11. Delete Method a.) dta_projRecordsetDelete b.) dta_proj.RecordsetDelete c.) dtaproj.Recordset.Delete d.) dta_proj.Recordset.Delete 12. There is a formal process to do that in database modeling : 12. There is a formal process to do that in database modeling a.) Normalization b.) Data c.) DataField d.) DataCode 13. The basic form properties and saved it, is called the : 13. T he basic form properties and saved it, is called the a.) Data Control b.) Data c.) DataField d.) DataCode 14. Title of this tutorial: 14. Title of this tutorial a.) Database Design b.) Data Security c.) Data Field d.) Data Code 15. HCI means? : 15. HCI means? a.) Humanistic Computer Intelligence b.) Human-Computer Interaction c.) Humans Compute Interactive d.) Humane Computerize Intelligence PowerPoint Presentation: SORRY, YOU’VE CLICKED THE WRONG ANSWER Click HERE PowerPoint Presentation: YOU’VE MADE IT!! CONGRATULATIONS!! KEEP IT UP!! click here