Hw_dbms_21 To Last Date

21-05-2008 Enhanced Entity Relationship Diagram: It is used when we have a lot of entities that have some similar attributes. We use two types of entities in this diagram: A. Super Type Entity B. Sub Type Entity Super type entity has all the attributes that are common among the entities. Sub type entities have specialized attributes that are specific to them and these are not present in the super type entity. Example: We can take the example of a hospital. There are two types of patient’s resident patients and residents patients. Now the “patient” can be the super type entity and the two types can be its sub types.

PASTE PIC OF SLIDES OF EERD Normalization: In simple words it is the process of making more tables from a single data. In this we make tables of the entities that are more complex and which should be further divided into simpler forms. It has some steps for normalization, called the 1st, 2nd …. Normal Forms. In FIRST NORMAL FORM all the multi valued attributes are removed. Then in the SECOND NORMAL FORM the relations in the first normal form are FULLY FUNCTIONALLY dependent on primary key. Now in the THIRD NORMAL FORM relations in the second normal form is not transitive functional dependency OR is any non-key attribute is dependent upon non-key attributes. Like this there are nearly 7 SEVEN different normal forms.

De-normalization:

It is the reverse of the normalization. In this step we move from more tables to lesser tables. If we feel that we are making some extra tables, we merge some tables back that we created in the normalization.

Anomalies: These can be called as the problems of the design. For example you delete one record from the database and the other records are also deleted. It is an example of the deletion anomaly. In the similar way, we have updating and inserting anomalies.

Entity clustering: Entity clustering is making clusters in the entity relationship diagram. When there are many relations in the entity relationship diagram, then we can make some portions or clusters of the entities so that these can be easily read and the entity relationship diagram becomes more easily readable.

23-05-2008 ER Win: It is the case tool designed to make ERD. From this we can also create the database and queries that can create forms for us. In ER Win the dotted line shows the non-identifying relationship. CASE means Computer Aided Software engineering. We normally use non-identifying relationship in drawing ERD. Every attribute has a domain. We can call a domain as the data type of the attribute. Validation rules can also be used in making the ERDs. In recursive relationships Role name is important.

Referential Integrity: It is the integrity that ensures that when in some table a foreign key is deleted, it will not allow deleting that data. It keeps the data connected with the tables that have referential integrity. We can do this in the ER Win quite easily. We can do this by double clicking the relationship line and going to Referential Integrity tab and choose Parent Delete (restrict and cascade).

28-05-2008 Data dictionary: RDBMS create system tables. We can take them as the example of the data dictionary. This data dictionary can be accessed by the owner of the database. By using the select statement we can get these data dictionaries.

3 Tier Architecture: A client / server configuration that includes: • Client layer • And two Server layers.

It has the advantage of the flexibility, reusability, performance and increased scalability. It also has a concept of thin and fat client. A computer that is configured for handling user interfaces and some application processing usually with no or limited local data storage.

29-05-2008 Look up: It is a column separated into a new table to save space. For example we can take the example of the job table. We can take out the distinct jobs from the job table and use them with the employees. It will space time and also the space of the data base.

MS Access: Create a project by the use of the new and choose project from the type of the file. Project has all the things other than data and data is in the database. Creating query by graphically is very easy. Select create from the menu. Choose the query and select the table from the menu. Now create the query by selecting the desired columns on which you want to apply the query. We can create the queries in the MS Access by simply selecting the columns for the query. We can also use the scalar functions in the access.

30-05-2008 MS Access Functions: Math: The simple mathematical operations like: • OPERATORS: +, -, *, /, \, mod, ^ • Round(number [,numdecimalplaces]) this function will round the number that is written in the braces, and in the next bracket the number of rounding of values in entered. • Int(number) this function will return the integer value of the value entered. • Sqr(number) this function will give us the square of the value that is entered in the bracket. • Sin(number) this function will give us the sin value of the numerical value. It helps to get the sine of number easily. • Cos(number) this function will give us the cosine of the numeric value. • Tan(number) this function will give us the tangent of the value that we enter in the bracket of this function. • Log(number) this function will give us the logarithm of the numeric value. • Rnd([number]) this is usually used in code not query. In the text functions: • OPERATORS: & OR + .“&” (ignore nulls) OR “+” (nullify result) • Len(string) it will give the length of the string entered. • LCase(string) it will change the string in lower case.

•

UCase(string) it will change the string upper lower case.

• • • • •

Left(string, number) it will give us the number of characters from left. Right(string, number) it will give us the number of characters from right. Mid(string, start, [length]) it will give us the number of characters from mid. InStr(start, source, tosearch) it is used to search some character from the string. LTrim, RTrim, and Trim it is used to trim the string, it can cut the string up to the limit that we specify. In the time functions: • Date() this function inserts the current date. • Time()this function inserts the current time in hours minutes and seconds. • Now()this function inserts the current date and time. • Timer() Returns the number of seconds elapsed since midnight • Day(Datetime) this function give us the day of the date. • Month(Datetime) this function give us the month of the date. • Year(Datetime) this function give us the year of the date. • Hour(Datetime) this function give us the hour of the date. • Minute(Datetime) this function give us the minute of the date. • Second(Datetime) this function give us the seconds of the date. • Weekday(Datetime, [firstdayofweek]) this function tell us the day of the week. • DateAdd(interval, number, date) this function is used to add the date into other date. • DateDiff this function is used to get the difference between two dates.

05-06-2008 Solved the quiz.

06-06-2008 MS Access Forms: Forms are used for the input purposes. We can get input from the users. It is also used to show the data. So forms perform two types of the purposes one is the input and the other is the output. But these are normally used for the input purposes. In forms we have form header and footer. These two things never change in the form. The Detail section is changed every time the now record is entered or we move to the next record. To go the properties of the form, that is the most important part of any MS access object, click on the top left corner of the form. Select the data tab and the in the record source of this select the table you want to attach your form with.

11-06-2008 MS Access Forms:

In access we must give name to every object. Now we take a text box from the tool bar of the program. Drop it on the form and checked it in the form view. Now right click on the text box and select the properties option. Now from this property option, select the source of the text box. Now it will show some column name of the table that you have already selected table from the form properties. We can place an object by using the OLE object on the form. It will adjust the picture in the area of the object. From the tool bar we can also select the button of logo and drop it on the form. Now can place our company logo on the form. Now the buttons are the most important thing on the tool bar. These buttons can perform several actions for us when we attach them with some macros of our choice. The sub form and sub report are two other important buttons. Drag and drop this button on the form. Now from this we can go to the properties of the sub form and select whether this form is read only or to allow additions or to allow the edit options for this sub form. With this we have to choose the Master Field for this form. Or if we not select the master field for the sub form we can get the whole form instead to get our desired data that will give us the targeted data.

12-06-2008 Macros in MS Access: A macro is sequence of actions. In ms access we are given an option to create some actions according to our requirement. These macros are then attached with the buttons and these button on the forms do the actions for us. To create a macro, go the create menu on the main menu. Choose the macro and create it in the design view. Now we have some actions and the arguments and comments in front of us. We can add the columns of the macro name and some other. Now in the columns we have the column of the actions. This is the basic thing in the macros. We have to choose the specific from this list and then we will have some suitable macro name in the macro name column. The column of arguments is the related column of the actions. We set the arguments according to the actions. For example some useful actions are: BEEP: This action gives us a sound from the computer when this macro is executed on some button or on some other action. CLOSE: This action will close the current active thing, it can be a form or a report or some other access object. NEXT RECORD: This action is usually used in the record selection purpose. This macro will take us to the next record of the data. PREVIOUS RECORD:

This action is usually used in the record selection purpose. This macro will take us to the previous record of the data. FIRST RECORD: This action is usually used in the record selection purpose. This macro will take us to the first record of the data. LAST RECORD: This action is usually used in the record selection purpose. This macro will take us to the last record of the data. OPEN FORM: This function will open some other form. QUIT: This action closes the MS Access and also closes the application currently running in access. RUN COMMAND: This action is a combination of many useful actions. When we see in the arguments of this we see many important and commonly used functions in this. • Clear all • Close • Close window • Connect database • Copy • Cut • Delete • Doc maximize • Doc minimize • Insert object • New database • Page setup • Redo • Refresh • Save record • Send • Send record • And many other action. SET PROPERTY: This action is used when we want to show some text box and then show it on some other event.

SET VALUE: This action is also used when we want to show some text box and then show it on some other event.

13-06-2008 MS Access Reports: Reports are basically for the output purposes. These normally are prepared for the management for the overview of the results of the business. Again like in the forms we have report header and report footer. The header and footer do not change in the report thorough out the report and the detail section is repeated in the report. From the tool bar we can also select the button of logo and drop it on the form. Now can place our company logo on the report. In access we must give name to every object. Now we take a text box from the tool bar of the program. Drop it on the form and checked it in the design view. Now right click on the text box and select the properties option. Now from this property option, select the source of the text box. Now it will show some column name of the table that you have already selected table from the report properties. SORTING: We can create the sorting of data on the reports. These is done in the reports that make the report more readable and more easily understandable. GROUPING: Just like we did in SQL, we can also create the groups in the MS Access. These are created in the reports that make the report more readable and more easily understandable.

Making MDE: To make the MDE file we go to the database tools and select the make MDE button on the tool bar. By doing this all the macros and reports and procedures become read only. Then you can restrict the use of the database by changing the setting of the users and permissions. Now we should change the extension of the file from ACCDB to ACCDR to make it more reasonable and now the forms and reports can be used and the only mode is available and not all other layout or the design view.

18-06-2008 Sub-sheets in MS Access: We can turn off the sub-sheet in access. Go the design view of the form and go to general tab, and from the sub-sheet choose none. Now we will not see the “+” sign on the left side of the table in the form. The same process can be done with the tables.

Parameterized Query: It’s example is: FORMS! FORM1.TXT.DEPTNO It is a re-query action in macro in the design view. We can get help from the access in using the macros. We can always have Expression builder or something like this that help us in doing our work in more easy manner.

19-06-2008 THE LAST CLASS  Discussed our projects.