Database Design And Development For Veterinary Surgery Computer Science Essay

Database Design And Development For Veterinary Surgery Computer Science Essay Petcare is a medium sized veterinary surgery with six branches across London. There is the entity relationship model of the data held by Petcare. Petcare want a database system developed to handle the records of the animals they care for, prescriptions and appointments. According to an initial analysis of Petcare, the entities include owner, animal, breed, animal type, appointment, veterinary doctor, branch, prescription, drug and drug type. Owner and Animal is One to Many Relationship All animals have an owner. Breed and Animal is One to Many Relationship Animals are defined by particular breed. Animal Type and Breed is One to Many Relationship Animals are defined by type (dog, cat, rabbit, etc) and also by particular breed Animal and Appointment is One to Many Relationship (Optional) The appointments for each animal are based upon the owner request. Veterinary doctor and Appointment is One to Many Relationship (Optional) The appointments for each Veterinary doctor are based upon the diagnosis made and the charge made. Branch and Veterinary doctor is One to Many Relationship Appointments are at particular branches of Petcare. Appointment and Prescription is One to Many Relationship (Optional) The outcome of the appointment might be have a prescription or more than one prescription. Prescription and Drug is One to Many Relationship (Optional) The outcome of the appointment might be a prescription with one or more drugs on it Drug Type and Drug is One to Many Relationship The drug must be taken for, based upon the drug type of prescription. Entity -relationship modeling database modeling methods used to produce the type of conceptual schema, or semantic data model, system, often a relational database and its requirements for top-down fashion. Entity-relationship models are used in the first phase of information system for clarify the types of information. And it is necessary to be stored in a database at the stage of requirements analysis. Animal Appointment Prescription Owner Veterinary doctor Drug Type Drug Animal Breed Animal Type Branch Definition of Optionality and Cardinality Symbols at the ends of the relationship lines indicate the optionality and the cardinality of each relationship. Optionality expresses whether the relationship is optional or mandatory. Cardinality expresses the maximum number of relationships. As a relationship line is followed from an entity to another, near the related entity two symbols will appear. The first of those is the optionality indicator. A circle ( â„ ¢ ) indicates that the relationship is optional-the minimum number of relationships between each instance of the first entity and instances of the related entity is zero. One can think of the circle as a zero, or a letter O for optional. A stroke ( | ) indicates that the relationship is mandatory-the minimum number of relationships between each instance of the first entity and instances of the related entity is one. The second symbol indicates cardinality. A stroke ( | ) indicates that the maximum number of relationships is one. A crows-foot ( ) indicates that many such relationships between instances of the related entities might exist. Task 2 Table is a data (value), is the model of the vertical columns (which identifies the name) and the horizontal lines. A specified number of columns in the table, it may be any number of rows. Each row to identify the subset of the values in the column, which has been identified as a candidate key. Table in another term relationship, although there is the difference that a table is usually a multi-set (bag) as a series, and does not allow copies. In addition, the actual data rows, the panels are generally associated with some other meta-data, such as restrictions on the table or the values in columns. Primary key: Primary key is a field / combination of fields. It is uniquely identify a record in the table, so each tag can be placed without any confusion. Primary key is the field (s) (primary key can be made up of more than one field). It is uniquely identifies each record, such as the primary key is unique to each record and the value will never be duplicated in the same table. A constraint is a rule that defines what data are valid for the area. So the primary key constraint is the rule which says that the primary key field can not be empty and can not contain duplicate data. Database systems always have more than one table, and these are usually related in any way. For example, a customer table and an Order table relate to each other on a unique customer number. The customer table will always be a record for per customer, and the Order table has a record for each order Foreign keys: A foreign key (sometimes referred to as the reference key) is a key for link two tables together. you will have a primary key field from one table and paste it into another table, which becomes the foreign key (the primary key in the original table). Foreign key constraints that the data in the foreign keys must be consistent with the primary key of the table are linked. This is called reference integrity is to ensure that data entered is correct and is not unique Owner Table Animal Table Owner Name Animal Name Owner ID Animal ID Owner Address *Animal Type Owner Home Telephone Number *Animal Breed Owner Mobile Telephone Number Animal Sex Animal Age Appointment Table Appointment Number Animal Type Table Appointment ID Animal Type ID *Animal Dog *Owner Cat *Veterinary Doctor Rabbit *Branch Appointment Time and Date Animal Breed Table Appointment Diagnosis Made Animal Breed ID Appointment Charge Made Outcome Prescription Table Prescription ID *Drug Type Veterinary doctor Table Prescription Period Veterinary Doctor ID Prescription In Days Veterinary Doctor Name The drug must be taken for Veterinary Doctor Address The cost of the medication Veterinary Doctor Home Telephone Number Drug Table Veterinary Doctor Moblie Telephone Number Drug ID *Branch Drug Names *Specialise Drug Type Table Branch Table Drug Type ID Branch Name Drug Type Names Branch ID Branch Address Branch Telephone Number Branch Opening Hours Branch Emergency Contact Telephone Number In Owner Table Primary key Owner ID In Appointment Table Primary key Appointment ID Foreign keys *Animal *Owner *Veterinary Doctor *Branch In Veterinary doctor Table Primary key Veterinary Doctor ID Foreign keys *Branch *Specialise In Branch Table Primary key Branch ID In Animal Table Primary key Animal ID Foreign keys *Animal Type *Animal Breed In Animal Type Table Primary key Animal Type ID In Animal Breed Table Primary key Animal Breed ID In Prescription Table Primary key Prescription ID Foreign keys * Drug Type In Drug Table Primary key Drug ID Drug Type Table Primary key Drug Type ID Task 3 Tables of Data Hold by Petcare MS Access is chosen to set up the tables of data hold by Petcare . Owner Table Appointment Table Veterinary doctor Table Branch Table Animal Table Animal Type Table Animal Breed Table Drug Table Drug Type Table Normalization is a set of rules .It can be used to modify the way data is stored in tables. Normalize the process of converting complex data structures into sample, stable data structures. It is the process of efficiently organizing data in a database. The advantage of the normalization process is to reduce data redundant and ensure data dependencies make sense. Normalization has the following steps: Gathering data, choosing a key, converting to first normal form, converting to second normal form, converting to third normal form, BCNF, 4NF, 5NF and domain-Key NF. 5NF and DKNF are not particularly applicable in database design. Normalization is a bottom up approach to database design, The designer interviews users and collects documents reports etc. The data on a report can be listed and then normalized to produce the required tables and attributes. 1NF This is the basic level of normalization, and it generally corresponds to the definition of any database, namely: It contains 2D tables with rows and columns. Each column corresponds to a sub-object or an attribute of the object represented by the all entire table. Each row represents a unique instance of that sub-object or attribute and must be different to some way from any other row. All entries in all columns must be of the same kind. 2NF -Second normal form (2NF). At this level of normalization, each column in a table is not to determine of the contents of another column must itself be a function of the other columns in the table 3NF-Third normal form (3NF). In the second normal form, modifications are still possible; it is because a change to one row in a table may affect data that refers to this information from another table. In the third normal form, these tables will be divided into two tables so that product pricing would be tracked separately Task 4 DBMS is a collection of software programs, the organization, storage, management and retrieval of data in a database. DBMS are categorized according to their structures and data types. It is a series of programs that are used to store, update and retrieve a database. The DBMS accepts requests for data from the program and shows the system to the appropriate data. When a DBMS is used, information can be changed much easier than organizing the information requirements change. New types of data may be included in the database, without the existing system. Organizations may be a kind of DBMS for daily business and then in detail on another computer, with another DBMS better suited for random inquiries and analysis. Overall systems design decisions are covered by the data administrators and systems analysts. Detailed database design is supported by the database administrators. Database servers are computers that have the actual database, and run only the software and the associated DBMS. Database servers are usually multiprocessor computers, with generous memory and RAID disk arrays for stable storage. Connected to one or more servers via a high-speed channel, hardware database accelerators are also available in large quantities of transaction processing environments. Database systems can be found in the heart of most database applications. Sometimes database systems to a private multitasking kernel with built-in networking support although nowadays these functions are left to the operating system. A DBMS consists of four parts: modeling language, data structure, database query language, transaction and mechanisms: Components of the DBMS DBMS Engine accepts logical request from the various other DBMS subsystems, converts them into physical equivalent, and in fact to the database and data dictionary as they appear on a single device. Data definition subsystem helps users to create and the data dictionary and the structure of the files in a database. Data manipulation subsystem helps users add, modify and delete information in a database query and for valuable information. Software tools within the data handling subsystem is usually the primary interface between users and the information contained in a database. It allows user to the logical requirements. Application number generation subsystem includes facilities, the user transactions to develop applications. It usually requires that users with a detailed series of tasks to a transaction. IT facilities easy to use input masks, programming languages, interfaces and data management subsystem .* helps users to manage the database environment by providing facilities for backup and recovery, security management, query optimization, concurrency control and change management. Five components of the DBMS DBMS Engine accepts logical request from the various other DBMS subsystems, converts them into physical equivalent, and in fact to the database and data dictionary as they appear on a single device. Data definition subsystem helps users to create and the data dictionary and the structure of the files in a database. Data manipulation subsystem helps users add, modify and delete information in a database query and for valuable information. Software tools within the data handling subsystem is usually the primary interface between users and the information contained in a database. It allows user to the logical requirements. Application number generation subsystem includes facilities, the user transactions to develop applications. It usually requires that users with a detailed series of tasks to a transaction. IT facilities easy to use input masks, programming languages and interfaces. Data Administration Subsystem helps users to manage the database environment by providing facilities for backup and recovery, security management, query optimization, concurrency control and change management. Access has become an industry standard in desktop and database engine is very powerful. Integration with speech recognition capabilities, the data and menus very easy. There are a large number of templates, including the one you can download online, what the creation of new databases very easy. The ability, they can not just be productive quickly, but you can also use things that meet your specific needs. Connectivity options are an advantage Access databases can connect to Excel spreadsheets, ODBC Connections, SQL Server and SharePoint Services sites for the live data. Tables in these sources can be linked and for the preparation of reports. Structured Query Language Structured Query Language (SQL) is a language that the computer database for managing data in relational database management systems (RDBMS). Its scope includes data query and updating of schema creation and modification, data and access control. SQL was one of the first languages for Edgar F. Codd s relational model in his influential paper, A Relational Model of Data for Large Shared Data Banks and was the most widely used language for relational databases. Many-to-Many Relationships A many-to-many relationships we have created a situation in which many companies cases are linked to many other business entities. The only way to resolve this situation and enforce the principle of minimizing redundancy of normalization is to create an intermediary table containing the primary key from the above tables. Creation of multiple connections between two tables would cause the replication of data, which is bad and will interweaving relationships can definitely wreck every good program you have in mind. The solution would be to create a third table, which as a cross-reference table. This cross-table (commonly known as X-REF) is the primary key columns from the previous two tables, and thus we have a relationship, when the X-REF table is a child table with the two parent companies earlier tables. We map the manifold relations of a third relationship in a third table. Therefore, we are opposed to our task of producing two individual one-to-many relationships, that are foreign keys relations with the parent table primary key. Microsoft Access Database Microsoft Access, you can easily create databases, store and present your data in forms and reports. When starting a database can be very simple and trivial, but over time, it may be critical, as you have more data, functions, and even share them with others. It gains a life of its own and the design will be of crucial importance. One of the main architectural designs is splitting the database into a front-end and back-end database. This is the way access has been designed so that you support multi-user databases to significantly simplify and improve how you use over time. Reasons for using a split database architecture Without a separate database architecture, you must update the database with the latest data that people have with each new release. Application number expansions are simplified because they are the front-end database without worrying about changes to the data in the back-end database. Release of new versions and bug fixes will be much easier, since only part of the application must be distributed. Of course, if you change table structures or add / delete / rename the tables, you must apply these changes on the back-end database. Performance can be significantly improved and the network traffic is reduced, if the user has a copy of the front-end database on the desktop instead of from the net every time they use it. Temporary tables can be kept for each user in the front-end database. This avoids collisions between multiple simultaneous users, if they were all equipped with a database. Without sharing a database, multiple users with the same database on the network increases the chance of database corruption. The split database design minimizes the problem and prevents code corruption of data impact of corruption. This simplifies database administration, because the data is centrally stored and backed up and compressed. A single master front-end database application will be copied to each user of the machine, but it is not necessary to. Provides the opportunity to create a database on the size of 2 GB size limitation of access as the front-end database can be found on several back-end databases, if necessary. Sets the stage for migration to SQL Server. If the application needs the functionality of SQL Server, you can use the front-end database to link to data stored in SQL Server. The access is for desktop use, more like a personal database. There can be multiple users in a workgroup in order, the total number of users (usually around 50 or so at a time) is small, however. This means that access is more useful for individual departments or the SMB (small and medium business sectors). Access also has difficulties with databases larger than 2 GB in size, but just to be safe you should limit use to about 1 GB. As you scale up the size, performance is slow (almost to the point of unresponsiveness). Use of multimedia data, even your digital camera photos can eat place very quickly. Until the 2007-version came on, even the way the pictures and other facilities for storage in Access databases to a bloat. Although the investment in Access 2007 takes care of that 2 GB of space could be very fast. Many pointed out that the SQL Server database is a real, as they compete with enterprise-level databases such as Oracle. Another difficulty pointed out by many, as the publication of anything other than static files is a problem with Access. It takes some work to access the data interactively. But you can SharePoint, a significantly large investments. Many believe that the SQL in MS Access is not as robust as the other databases. It is a very widespread belief that access is geared to developers than end users. Microsoft Access is an well organized development environment used to create computer-based databases. It also includes a programming language called Visual Basic For Application (VBA) and various libraries. This language and the libraries are used in a programming environment called Microsoft Visual Basic, which also includes in Microsoft Access. Microsoft Access 2007 is a full-featured database application which users can use it to manage, track and share information from multiple sources. It lets users easily create a user-friendly database for storing business or personal records, such as addresses, business partners and business transactions. Task 5 The use of data base management system When analyzing the Entity Relationship Data Model, it is quite easy to garble roles. We need to be more careful to identify them. When choosing data base management system (DBMS) to set up the normalized tables. There are many DBMS software surrounding the internet, e.g. Oracle, MySQL, Ms Access, ASP, etcà ¢Ã¢â€š ¬Ã‚ ¦Ãƒ ¢Ã¢â€š ¬Ã‚ ¦ In this case, We choose Microsoft Access because it has its own format based on the Access Jet Database Engine. It can import or link easily and directly to any data stored in other Access databases, excel, Share Point lists, text, XML, Outlook, HTML, dBase, Paradox, Lotus 1-2-3, or any ODBC-compliant data container including Microsoft SQL Server, Oracle, MySQL and PostgreSQL. We can use it to develop application Software and non-programmer power users can use it to build simple applications. It also supports some object-oriented techniques but falls short of being a fully object-oriented development tool. Microsoft Access is part of the Microsoft Office suite and is the most popular Windows desktop database application. It is targeted for the information worker market, and is the natural progression for managing data when the need for a relational database arises or after reaching the limits of Microsoft Excel. Object-orientation and databases Both object-oriented programming and relational database management systems (RDBMSs) are extremely common in software today. Since relational databases do not store objects directly (though some RDBMSs have object-oriented features to approximate this), there is a general need to bridge the two worlds. The core of object-relational thinking is the ability to incorporate greater levels of abstraction into data models. This idea represents a major shift in the way that data modelling is done. Current relational databases are usually highly normalized but with little abstraction. Each thing of interest is instantiated as a relational table. As a result, systems frequently require numerous database tables and an equal number of screen modules and reports. The program modules are usually based directly on these tables with user workflow only instantiated through the way that the hundreds of screen modules interact. The object-oriented (OO) approach to data modelling will be something of a change for people familiar with entity relationship modelling. Even though we still end up with tables and relationships at the end of the process, the way to think about the modelling process has to change. Object-relational data models have several advantages over traditional data models: They requir e fewer entities (or classes in object-oriented terminology); They are more robust, in that they will support not only the specific user requirements gathered during the analysis phase, but will also usually support a broader class of requirements; They are more stable in that, as new requirements arise, the models will require fewer changes than traditional models Improvement Data is not information unless it is valued. Information value provides profit or gain only when accessible or used. Accessibility and use, through organized systems, provides competitive advantage. Speed determines the degree of competitive advantage. Computerized database systems are thus, the ultimate method of high-speed information retrieval. It is not difficult to build an organized database system. The difficulty lies in the laborious, mundane task of collecting, categorizing and maintaining the massive amounts of data. Information is not valued unless it is legitimate. It must be valid and true to be worthy of use in decision-making. So, it is critical that all aspects of our system provide quality. To quote statistics based on erroneous data is considered foolish or criminal. The core of object-relational thinking is the ability to incorporate greater levels of abstraction into data models. This idea represents a major shift in the way that data modelling is done. Current relational databases are usually highly normalized but with little abstraction. Each thing of interest is instantiated as a relational table. As a result, systems frequently require numerous database tables and an equal number of screen modules and reports. The program modules are usually based directly on these tables with user workflow only instantiated through the way that the hundreds of screen modules interact. Based on the captioned analysis, we can change the approach to improve the work for setup of database in more effec tive way. There are a several database program applications for development and SQL database is very powerful tool. We can create the tables and query under SQL to setup the relationships of tables for generating the analysis reports. The database security issue is very important to protect the data and ensure that the database systems are secure from unauthorized access. Database security is normally assured by sing the data control mechanisms available under a particular DBMS. Data control comes in two parts: preventing unauthorized access to data, and preventing unauthorized access to the facilities of particular DBMS. Database security will be a task for the Database Administrator normally conducted in collaboration with the organizations security expert. The performance is a relativistic concept. A volume analysis estimates of the maximum and average number of instances per entity. A usage analysis a priorities list of the most important update and retrieval transactions expect ed to impact on the applications data model. For the integrity analysis inherent integrity constraints and most important domain with additional constraints can be specified in an associated data dictionary. The database systems have become so important to organizations that the activity is devoted to planning for, monitoring and administering the systems. We can focus on the planning and managerial activities relevant to database. It is defined the concept of data administration, the scope of the data administration function, relate the costs and benefits of having a data administration functions. It also defines the concept of a data dictionary and considers the issue of database security. The data control is primary function for the database administrator (DBA). The DBA needs to be able to do three main things: Prevent would-be users from logging-on to the database Allocate access to specific parts of the database to specific users