StudentShare
Contact Us
Sign In / Sign Up for FREE
Search
Go to advanced search...
Free

Conceptual And Logical Database Design - Assignment Example

Cite this document
Summary
The reporter casts light upon the fact that a database entails a component where data is stored. Data basically are blocks of information. Database systems and design focus on the creation and implementation of a database to suit an information system…
Download full paper File format: .doc, available for editing
GRAB THE BEST PAPER96.1% of users find it useful
Conceptual And Logical Database Design
Read Text Preview

Extract of sample "Conceptual And Logical Database Design"

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
Cite this document
  • APA
  • MLA
  • CHICAGO
(Conceptual And Logical Database Design Assignment, n.d.)
Conceptual And Logical Database Design Assignment. Retrieved from https://studentshare.org/information-technology/1790981-database-oracle-sql
(Conceptual And Logical Database Design Assignment)
Conceptual And Logical Database Design Assignment. https://studentshare.org/information-technology/1790981-database-oracle-sql.
“Conceptual And Logical Database Design Assignment”, n.d. https://studentshare.org/information-technology/1790981-database-oracle-sql.
  • Cited: 0 times

CHECK THESE SAMPLES OF Conceptual And Logical Database Design

Accommodation Services for the Language Summer School

These entities form the master entities and others will follow from the logical and conceptual relationships between these entities. b.... This can also be called a logical model as it does not take into consideration any DBMS in mind and shows the logical association of the system.... ext we are constructing the logical system which has the cardinality ratios.... logical modelThe logical model is as follows:The difference here is that the entities here are associated with their relationships with the cardinality ratios respectively....
6 Pages (1500 words) Essay

Investigation into Uptake of COBIT Corporate Governance Auditing Methodology

with Your Name, Supervisors Name, University Name, Module code, Module title, Module Leader, Your Name, SID Number, etc.... (whichever applicable)> As an outcome of the analysis of the world famous Enron and… derson Scandal in late 2001, the US Government introduced Sarbanes Oxley Act 2002 that enforced a number of enhancements in the methodology of Corporate Governance and also ensured better accountability of auditors in control of corporate frauds....
5 Pages (1250 words) Essay

Online Recipe and Meal Planner

In includes a database (MS Access 2007) that stores the meal recipe, ingredient and costs and a web interface (Asp.... et and C#) that interacts with the database.... The structure of database has to be designed in a way to comply with issues like data integrity.... So the main focus has to be on the relational database and normalisation.... The reliability of this system will be achieved when all the tables in the relational database are of Third Normal Form (3NF)....
28 Pages (7000 words) Essay

You may pick one to use

he design phase helps determine how the system is to be built and what technology will be used.... System modeling is also done during design.... The construction phase relies majorly on the analysis and design phase in order to be successful.... A database will also come in handy to save user records and the materials they can access online.... For a virtual online library, the database may store the library users' login and usage information....
2 Pages (500 words) Research Paper

Database Administration -2

Therefore, the design of the database should be accurate and reliable that will not only help to achieve new business ideas, but also play vital role in promoting the organizational goals.... The database Administrator is responsible for establishing effective control over the database, moreover, the performance/efficiency, integrity or correctness and security of an information system database.... However, depending on the experience and organization the role and… The following list provides all the required skills of the database administrator for information system database of large organizations (Kioskea, The database administrator would have the above given skills in order to perfectly perform his/her roles and responsibilities to manage the information system database in an organization....
4 Pages (1000 words) Essay

Three Level Architecture

The aim of the paper “Three level architecture” is to evaluate numerous systems design methods for consistency and scalability.... hellip; According to the essay, the lessons of DBMS structure design are not as generally recognized as they should be.... database Management Systems or DBMSs are mission-critical and complex applications.... The community of people concerned in planning and application of database systems is tense: numerous concentrated on the similar schools, worked on the similar research projects, plus work jointly on the similar profitable products....
10 Pages (2500 words) Essay

Information Systems in Business

This relationship benefits the organizations in different ways, one is that it makes it easy to understand the picture of the current data, allow data to be independent and enable development of logical design.... This relationship assists in linking up the information during the extraction from different tables in database.... database has different models, the hierarchical model, Network Model, and Relational Model.... There are three types of databases, multimedia database, object-oriented database, and On-Line database (week 2)....
2 Pages (500 words) Coursework

Implementation of a Resource Center

and logical model in Fig.... The entity-relationship model was used to design the data base that will store & organize the resource center data.... The entity-relationship design:It's evident that the physical objects from the previous part (community members, resource centre card, resource centre staff, books & DVD's and resource centre branches.... Nonetheless the design is aimed at minimization of redundancy and storage of the necessary information in a small space as possible....
3 Pages (750 words) Assignment
sponsored ads
We use cookies to create the best experience for you. Keep on browsing if you are OK with that, or find out how to manage cookies.
Contact Us