Databases Issues - Assignment Example

Databases 1). Create and submit an ERD for this database that you use as the basis of your implementation. In the case at hand ERD for this problem we will have , The entities are water_In_supply entity, Dripwater entity and meter_Details entity, client_details entity and client_meter_details_entity. The water_In_supply entity has a many-to-many relationship with the Supplier entity because one supplier can supply many items and many suppliers may supply one item itself. The water_In_supply entity also has a many-to-many relationship with the meter_Details entity because different meters may be supplied. After normalizing the data we get five tables for the five entities. The attributes of the five tables are listed as columns in each of these tables. Each table has a primary key to identify each item in the table and a foreign key to cross-reference one table with another. A double-edged arrow indicates a many-to-many relationship and a straight-line indicates a one-to-one relationship. 2). A one paragraph explanation as to the changes you have made to the ERD on the basis of your feedback from Assignment 1(feedback pic attached) or as a result of having to support the transactions and views below.(5 marks) The E-R diagram above follows the top-down approach and it is follows a logical schema of designing a database. By using database normalization I have verified that the design meets user requirements. E-R diagrams are widely used in software engineering to determine the most appropriate design for database in a relational database system. Database normalization ensures that there are no duplicate fields in the database and only the relevant details are stored in a database. 3). Create a data dictionary that lists at least each of the tables, the columns, their domains and any other constraints that apply.(10 marks) Attribute MySQL Type Description ID(FK) Unsigned int(10) Foreign Key on meter reading(ID) Part_id(not null) Unsigned int(10) Landslide may consist more than one part. Comment Varchar(50) Comments on bills readers(not null) Double Employees responsible for meter reading bill(not null) Double Amount of litres consumed in a month 4). Implement the database in Oracle SQLPlus(attach codes/tables etc to me)(20 marks) table 5). Provide an explanation (and any code you used) as to how you implemented (or, if you were not able to successfully implement, how you would have implemented) the following constraints(20 marks): a). Meter readers can only read a maximum of 5 meters in any given calendar month (please note this constraint is modified from Assignment 1 so that you will be able to test it). DECLARE CURSOR FROM READERS; A rowtype; N number; BEGIN OPEN readers; SELECT MAX(5 meters) INTO N FROM readers; LOOP FETCH readers INTO A; EXIT WHEN readers NOTFOUND; DBMS_OUTPUT.PUT_LINE(N); SELECT MAX(5 meters) INTO N FROM readers WHERE readings Read More