Conceptual And Logical Database Design - Assignment Example

Contents 1Introduction 2 2Project Scenario 2 3Conceptual Database Design 3 3.1DFD Level 0 3 3.2The entity relational modelling 6 4Database Implementation 8 4.1Database creation and placing integrity constraints 8 4.1.1Creating tables 8 4.1.2Foreign Keys 10 4.1.3Inserting data 11 4.2Specific queries to the database 13 4.2.1Selection of Particular table columns 13 4.2.2Inner join and outer join of tables students and academic rep aspirants 14 4.2.3Count functions 15 4.2.4Sorting/ ordering facility 15 4.2.5Conditional Queries 16 4.2.6A sub query 17 5CONCLUSION 18 6REFERENCES 18 Database Systems & Design, Conceptual and Logical Database Design (EER Modelling) and Oracle SQL 1 Introduction A database entails a component where data is stored. Data basically is blocks of information. Database systems and design focuses on the creation and implementation of a database to suit an information systems. Without an efficient database, then we cannot have a successful information system. Conceptual database design focuses on the theoretical aspects of a database. It is the first crucial step in the modelling of any database. A logical database design analyses the flow of data in a database. It identifies the unique relationships that exist within various entities in an information system. Having viewed these key definitions surround database system, we will apply these principles in the modelling of a database system for our information system. We will model a database that will support a student’s electronic voting system. That will be our business scenario for the study. 2 Project Scenario The Campus student’s council voting system was initialised as a way of electing the students representatives. Due to the large numbers of the students, the current manual system calls for a sampling of student’s representatives from every faculty to form an Electoral College. This college would then meet and elect the council members. Thus, the students only participated indirectly in the election process. There is no doubt that this process undermines the whole process of democracy within the student population. This is worsened in cases where aspirants influence the vote of the Electoral College. In an effort to restore credibility within the election process, we will aim at creating a direct involvement of the whole student population in the election of their leaders. In order to handle the large populations, it therefore calls for an electronic voting system. This would not only create transparency but also shorten delays in election conduction period right from voting, to polling of results. For an efficient electronic system to exist, it calls for an expertly analysed database system. It will be the core of this system. For the purpose of this study, we will critically apply the fundamental principles involved in database design and modelling. 3 Conceptual Database Design A conceptual database design captures how data is modelled. This implies the use of entity relational modelling aspect. This approach will mean deciding the entities that will be part of the system. Linking these entities together and coming up with a suitable schema. We will do it in a step wise fashion starting from seeing how data flows. This calls for the analysis of data flow diagrams. All the levels that is level 0 to level 1, are important. 3.1 DFD Level 0 The voting system level 0 database schema is as modelled below. Figure 1- Level 0. DFD Level one logical Data Flow Diagram for the system is as shown below Figure 2- Level 1 DFD 3.2 The entity relational modelling The Entity relational model diagram for the database system is as shown below. We used MS Visio tool to model this schema. Figure 3- Entity relation model This shows a centralized schema of interlinking entities to the central pillar entity. We will breakdown the relationships within each entity to the central entity. The above entities show a one to one relationship with the Voters_Choice entity. This creates the rule that a student can only have once choice and can select the various candidates once. This confirms the integrity of the whole system 4 Database Implementation For this case study, we will use oracle Database management system to create the various tables from the entities we have modelled above. The primary keys will add data integrity while the foreign keys will implement the relationship schema for the entire database. 4.1 Database creation and placing integrity constraints Our database will be named Sysvoting. The various table will take the prefix “sysvoting_” which will be the system username. 4.1.1 Creating tables The tables were created as below Table Academic Representative Aspirants (Acarepaspirants) Create table acarepaspirants (IDNumber number (10), Acarepcode number (10), AspirantsFName varchar2 (255), AspirantsLName varchar2 (255), AspirantsCourse varchar2 (255), VoteCount number (20)); Adding primary key Alter table acarepaspirants Add constraint com_pk PRIMARY KEY (IDNumber, Acarepcode); Table Assistant academic Representative aspirants Create table assacarepaspirants (IDNumber number (10), AssAcarepcode number (10), AspirantsFName varchar2 (255), AspirantsLName varchar2 (255), AspirantsCourse varchar2 (255), VoteCount number (20)); Adding Primary key Alter table assacarepaspirants Add constraint comass_pk PRIMARY KEY (IDNumber, AssAcarepcode); So the same SQL syntax will apply for table’s assportsrepaspirants, chairrepaspirants, financerepaspirants, publicrepaspirants, secretaryaspirants, sportsrepasspirants, and vicerepaspirants. The only variant column is the aspirants code column. For table students, we will use the following SQL Create table students (IDNumber number (10), Acarepcode number (10), FName varchar2 (255), LName varchar2 (255), Course varchar2 (255)); It will have IDnumber as its primary key as follows; Alter table students Add constraint students_pk PRIMARY KEY (IDNumber); For table Voterschoice it will be created as follows Create table voterschoice (IDNumber number (10), Acarepcode number (10), AssAcarepcode number (10), Sportsrepcode number (10), AssSportsrepcode number (10), Financerepcode number (10), Publicrepcode number (10), Secrepcode number (10), Chairrepcode number (10), Vicerepcode number (10)); All the columns will serve as composite key as follows Alter table voterschoice add constraint voterschoice_pk PRIMARY KEY (IDnumber, Acarepcode, AssAcarepcode , Sportsrepcode , AssSportsrepcode , Financerepcode , Publicrepcode, Secrepcode , Chairrepcode , Vicerepcode ); 4.1.2 Foreign Keys To link up the various tables we will use foreign keys. The first linkage will be the various aspirants tables to students table this is as shown below ALTER TABLE acarepaspirants ADD CONSTRAINT acarepaspirants_ibfk_1 FOREIGN KEY (IDNumber) REFERENCES students (IDNumber); ALTER TABLE assacarepaspirants ADD CONSTRAINT assacarepaspirants_ibfk_1 FOREIGN KEY (IDNumber) REFERENCES students (IDNumber); ALTER TABLE sportsrepaspirants ADD CONSTRAINT sportsrepaspirants_ibfk_1 FOREIGN KEY (IDNumber) REFERENCES students (IDNumber); ALTER TABLE asssportsrepaspirants ADD CONSTRAINT asssportsrepaspirants_ibfk_1 FOREIGN KEY (IDNumber) REFERENCES students (IDNumber); ALTER TABLE financerepaspirants ADD CONSTRAINT financerepaspirants_ibfk_1 FOREIGN KEY (IDNumber) REFERENCES students (IDNumber); ALTER TABLE publicrepaspirants ADD CONSTRAINT publicrepaspirants_ibfk_1 FOREIGN KEY (IDNumber) REFERENCES students (IDNumber); ALTER TABLE secrepaspirants ADD CONSTRAINT secrepaspirants_ibfk_1 FOREIGN KEY (IDNumber) REFERENCES students (IDNumber); ALTER TABLE chairrepaspirants ADD CONSTRAINT chairrepaspirants_ibfk_1 FOREIGN KEY (IDNumber) REFERENCES students (IDNumber); ALTER TABLE vicerepaspirants ADD CONSTRAINT vicerepaspirants_ibfk_1 FOREIGN KEY (IDNumber) REFERENCES students (IDNumber); After linking the aspirants table to the students table, we will proceed to link the voterschoice table to the other tables as shown below Alter table voterschoice ADD CONSTRAINT voterschoice_ibfk_47 FOREIGN KEY (ChairRepCode) REFERENCES chairrepaspirants (ChairRepCode), ADD CONSTRAINT voterschoice_ibfk_48 FOREIGN KEY (ViceRepCode) REFERENCES vicerepaspirants (viceRepCode), ADD CONSTRAINT voterschoice_ibfk_49 FOREIGN KEY (SecRepCode) REFERENCES secrepaspirants (secRepCode), ADD CONSTRAINT voterschoice_ibfk_41 FOREIGN KEY (FinanceRepCode) REFERENCES financerepaspirants (FinanceRepCode), ADD CONSTRAINT voterschoice_ibfk_42 FOREIGN KEY (PublicRepCode) REFERENCES publicrepaspirants (PublicRepCode), ADD CONSTRAINT voterschoice_ibfk_43 FOREIGN KEY (SportsRepCode) REFERENCES sportsrepaspirants (SportsRepCode), ADD CONSTRAINT voterschoice_ibfk_44 FOREIGN KEY (AssSportsRepCode) REFERENCES asssportsrepaspirants (AssSportsRepCode), ADD CONSTRAINT voterschoice_ibfk_45 FOREIGN KEY (AcaRepCode) REFERENCES acarepaspirants (AcaRepCode), ADD CONSTRAINT voterschoice_ibfk_46 FOREIGN KEY (AssAcaRepCode) REFERENCES assacarepaspirants (AssAcaRepCode); 4.1.3 Inserting data Once we have created the relationships we can insert sample data as shown We will start with students since it has a foreign key integrity to the other table hence it must contain data otherwise we may not be able to populate the other tables. The insert SQL statement to populate students table is as follows: INSERT INTO students (IDNumber, FName, LName, Course) VALUES (050100, James, ‘Wambugu’, BBIT), (050101, Mary, ‘Waihenya’, BCOM), (050102, Julian, Wanja, BBIT), (050103, Miriam, ‘Atieno’, BCOM), (050104, Bertha, Anyango, DBIT), (050105, Beatrice , Olembo, DBIT), (050106, Alice, ‘Swaleh’, CPA), (050107, Davis, Wengu, CPA), (050109, Agatha, Weku, ACCA), (050110, Aluora, ‘Acheiko’, DBIT), (050111, Belly, ‘Akinyi’, ACCA), (050112, Festus, Biwott, DBIT), (050113, Festus, Oketch, BBIT), (050114, Halima, Moha, DBIT), (050115, Katisya, Mueni, BBIT), (050116, Lilian, Mokeira, BCOM), (050117, Judy, Mogeni, BBIT), (050118, Elvis,Sadek, BCOM), (050119, Evans, Kamau, BBIT), (050120, Lukas,Ogi, DBIT), (050121, Sharon,Chebet, BCOM), (050122, David, Ochieng, BBIT), (050123, Simon, Omingo, CPA), (050126, Chebet, Faith, DBIT); The SQL to populate acarepaspirants as representative of the aspirants table is as follows INSERT INTO publicrepaspirants (IDNumber, publicrepcode, AspirantsFName, AspirantsLName, AspirantsCourse, VoteCount) VALUES (050113, 601, Festus, Oketch, BBIT, 10); (050114, 602, Halima, Moha, DBIT, 36); The SQL statement to populate voterschoice is as follows INSERT INTO voterschoice (IDNumber, ChairRepCode, ViceRepCode, SecRepCode, FinanceRepCode, PublicRepCode, SportsRepCode, AssSportsRepCode, AcaRepCode, AssAcaRepCode) VALUES (050102, 500, 150, 800, 600, 700, 900, 400, 200, 300), (050114, 500, 150, 800, 600, 700, 900, 400, 200, 300), (050116, 501, 150, 800, 600, 700, 900, 400, 200, 300), (050117, 501, 150, 800, 600, 700, 900, 400, 200, 300), (050120, 501, 150, 800, 600, 700, 900, 400, 200, 300), (050121, 500, 150, 800, 600, 700, 900, 400, 200, 300), (050122, 501, 150, 800, 600, 700, 900, 400, 200, 300), (050123, 500, 150, 800, 600, 700, 900, 400, 200, 300), (050100, 501, 151, 800, 600, 700, 900, 400, 200, 300), (050103, 501, 151, 801, 601, 700, 900, 400, 201, 301), (050104, 501, 151, 801, 601, 700, 900, 400, 200, 300), (050115, 501, 151, 801, 601, 701, 900, 400, 200, 300), (050126, 501, 151, 801, 600, 700, 900, 400, 200, 300); 4.2 Specific queries to the database In this section we will view the following sample queries; selection of particular table columns, inner join and outer join of at least 2 tables, use of count functions, sorting facility, conditional queries, and sub query in relation to the database Sysvoting. 4.2.1 Selection of Particular table columns With regards to table students we will select the Student’s course and the last name using the following query; Select course, LName From students; We get the results below. 4.2.2 Inner join and outer join of tables students and academic rep aspirants So using students and academic rep aspirants, we will do an inner join using the SQL below; SELECT students.IDNumber, students.fname, acarepaspirants.AcaRepCode FROM students, acarepaspirants WHERE students.IDNumber = acarepaspirants.IDNumber; We obtained the following results An outer join of the two table is as represented below Select students.IDNumber, students.fname, acarepaspirants.AcaRepCode From students, acarepaspirants Where students.IDNumber = acarepaspirants.IDNumber (+); The results output is as shown below 4.2.3 Count functions We will use students table and vicechair aspirants table. Let’s see the example below Select COUNT (LName) from students; The result is as shown For table vicerepaspirants we can use the function below; SELECT COUNT (vicerepcode) FROM vicerepaspirants; Results are 4.2.4 Sorting/ ordering facility We will sort out the students table using the following query to arrange the first name of the students in an ascending order as follows; SELECT FName FROM students ORDER BY FName ASC; Results is as shown below; 4.2.5 Conditional Queries For this case, we will use chairrepaspirants table to identify candidates who had more than 20 votes as follows. This aspect of query uses the greater or less than SQL statements SELECT * FROM chairrepaspirants WHERE VoteCount > 40; We get the output shown And where the VoteCount is below 40 is provided as follows SELECT * FROM chairrepaspirants WHERE VoteCount < 40; For a like condition clause, we will select all the students who are taking the Course BBIT. This is as shown; SELECT * FROM students WHERE course like BBIT; Results are shown below: An in condition statement, we will select those students taking either BBIT or DBIT course using the following query. SELECT * FROM students WHERE course in (BBIT, DBIT); The results are as shown. 4.2.6 A sub query Finally we will implement a sub query, which basically is a query within a query. We will use the example below Select * from all_tables tabs Where tabs.table_name in (select cols.table_name From all_tab_columns cols Where cols.column_name = FName); The results are no data available as shown below. 5 CONCLUSION We have modelled our entities into tables using the Oracle DBMS. We have seen how to create tables, implement integrity constraints, insert data, and query the data and so many functions. The database systems and design provides a logical criteria in coming up with an efficient database system right from the conceptual and logical database design to the actual implementation. For the case study we used a student’s voting system. We modelled the conceptual view, described the various entities and finally implemented it using Oracle DBMS. The different query capabilities of oracle makes it one of the most powerful database management software. Its security is high and has no lag time in query execution 6 REFERENCES Michael M 2011, PL/SQL Programming, Oracle-Press, BYU-Idaho. Morales T 2008, Oracle Database Reference 11g Release (11.1), Oracle. Read More