Prerequisites for Good Database Management - Assignment Example

? Database management Supervisor] ERD: Department: No Attribute PK FK Data Type Department ID P Alphanumeric 2 Department Name Varchar (100) 3 Country ID F Numeric Job: No Attribute name PK FK Data Type 1 Job ID P Alphanumeric 2 Description Varchar (100) 3 Qualification required Varchar (100) 4 Salary Numeric Product: No Attribute name PK FK Data Type 1 Product ID P Alphanumeric 2 Description Varchar (100) Inventory: No Attribute name PK FK Data Type 1 Inventory ID P Alphanumeric 2 Product ID F Numeric 3 Country ID F Numeric 4 Quantity Numeric Sales: No Attribute name PK FK Data Type 1 Transaction ID P Alphanumeric 2 Money Obtained Numeric 3 Products shipped Varchar (100) 4 Product ID F Numeric Employee: No Attribute name PK FK Data Type 1 Employee ID P Alphanumeric 2 Name Varchar (100) 3 Job ID F Numeric 4 Department ID F Numeric 5 Country ID F Numeric Country: No Attribute name PK FK Data Type 1 Country ID P Alphanumeric 2 Country Description Varchar (100) 3 Country Name Varchar (100) Justification for the selection entities and attributes: From the given scenario three tables are evident that is the product table, country and employee table. These three tables handle information regarding products being developed by the organization, countries in which it operates and employees that work for the organization. Since the organization sells various kinds of batteries it is evident that to maintain the record of the sales it makes it would need a sales table. The organization would also have to maintain the record of inventory and the products that are available. Finally to maintain records regarding the wages and designation of each employee a separate table of Job is created to help evaluate the position of an individual within the organizational structure. To help distinguish each entity from the other each record within each entity is assigned a unique alphanumeric character that helps in distinguishing one record from the other. Other entities that is present within a certain table further help in elaborating the data regarding a particular record. For example in the country table the attributes of country name and country description allows an individual to understand what the country ID stands for. These fields can significantly aid the organization when they are carrying out a presentation or are creating their annual reports that need to be presented to the organizational heads of the organization. Integrity rules: The relational tables are shown below: Product Inventory No Attribute name 1 Inventory ID 2 Product ID 3 Country ID 4 Quantity Sales No Attribute name 1 Transaction ID 2 Money Obtained 3 Products shipped 4 Product ID Employee Country No Attribute name 1 Country ID 2 Country Description 3 Country Name Department No Attribute name 1 Department ID 2 Department Name 3 Country ID Job No Attribute name 1 Job ID 2 Description 3 Qualification required 4 Salary The rules of integrity are given as follows: Primary keys for each record must be unique, cannot be null and must be in accordance to the data variable set for it. A unique primary key helps in distinguishing one record from the other. The value of a foreign key that is present within another table must be in accordance to the value that is present in the table where that particular field is the primary key. In short the value placed in a foreign key field must also be present in the field where it is a primary key. An employee cannot be assigned more than one country ID against his or her record. This means that an employee would only be assigned the country ID in which he or she works. This would help prevent any confusion that may arise from the fact that an employee is incharge of two departments that exist in different countries. Issues due to the use of un-normalized tables: The use of un-normalized database may cause a great deal of confusion while updating the data. If one of the users has accessed a particular record that is present in two different tables and modifies it then there is no guarantee that that record would be updated in the second data. This means that if a second user accesses that data from another table than the second user would receive an out-dated copy of the data. Furthermore un-normalized database also does not lock a file when it is being accessed by a particular user. This means that two users can amend the same file at the same time which would again cause incorrect data to be input within the tables. Normalized tables ensure that records that are same in nature are placed within a single table that allows only one user to access a particular file at a time. This significantly reduces the chances of entering incorrect values in the database. Moreover, since majority of data is placed in a single table it becomes easier to maintain all records and ensure that they are secure from unauthorized access. In un-normalized tables the retrieval time is much greater than that of normalized tables. Going through all records within different tables in order to access the required records could take a great deal of time. Since normalization of these tables would break down the tables into smaller less complex structures access time would be significantly be reduced and therefore the speed of processing would greatly increase. UNF: 1NF: No Attribute name 1 Product ID 2 Description No Attribute name 1 Inventory ID 2 Product ID 3 Country ID 4 Quantity No Attribute name 1 Country ID 2 Country Description 3 Country Name 2NF: No Attribute name 1 Country ID 2 Country Description 3 Country Name 3NF: No Attribute name 1 Country ID 2 Country Description 3 Country Name Inventory ID Country ID Quantity Product ID Description The normalization of the tables above shows the relationship between the tables named inventory and product. Since the relation between the two tables is that of many to many it is a feasible option to normalize these tables and form a primary key from two different fields. In this case the fields used to form the primary key is product ID and inventory ID. Under 3nf the combination of the values present in the two fields must be unique that is the combination of the values in these fields must not repeat itself again in the table. It is vital to mention here that instead of using the fields of product ID and inventory ID, Inventory ID could also be used. Normalization of this table allows the company to view the complete record of a product. This allows an employee to easily know about which product is present in which inventory in what quantity by viewing just one table. This would significantly save time that is usually taken in retrieving the information that is required by an individual. User management techniques: In order to ensure that a database performs at optimum efficiency it is vital that the organization employs a full time IT team that comprises of a database administrator. The administrator must emphasize the organization into normalizing as many tables as possible. Second technique that a database administrator must implement is allowing the database to grow without compromising the system’s performance. Usually a system’s speed decreases as the size of the data increases. This means that it is vital that a database administrator must always keep free space to ensure that there is room for new data to be stored on to the database. To make room for new data the database administrator must delete old useless data from the database after backing the data up. Another factor that affects the speed of the system is the available RAM space. Usually when a search operation is carried out there are a number of records that are loaded onto the RAM. Data that is mounted onto the RAM slows the speed of the system and affecting the overall performance of the system. To avoid such a phenomenon it is vital that database administrators continuously optimize their search algorithms. An optimum search algorithm would decrease the number of records that are generated as a result of a query. In order to ensure that a search algorithm is performing at optimum efficiency it is vital that these algorithms are tested on a regular basis and it is made sure that these algorithms not only generate the correct results but generate them in record time. Read More