...building blocks of modern database architecture. SQL defines the methods used to create and manipulate relational databases on all major platforms. At first glance, the language may seem intimidating and complex but it's really not all that bad. In a series of articles over the next few weeks we'll explore the inner workings of SQL together. By the time we're through, you'll have the fundamental knowledge you need to go out there and start working with databases! This week, our first article in the SQL series provides an introduction to the basic concepts behind SQL and we'll take a brief look at some of the main commands used to create and modify databases. Throughout this article, please keep our goal in mind: we're trying to get the "big picture" of SQL -- not a mastery of the individual commands. We'll provide a few examples for illustrative purposes and explain the theory behind them, but don't feel frustrated if you can't write your own SQL commands after reading this article. We'll cover each of the major commands in detail in future weekly installments. If you'd like a reminder in your e-mail inbox each week when the next article is posted, please take a moment and subscribe to our newsletter. By the way, the correct pronunciation of SQL is a contentious issue within the database community. In their SQL standard, the American National Standards Institute declared that the official pronunciation is "es queue el." However, many database professionals have taken to the...
Words: 1859 - Pages: 8
...the AIS. Study probs 1.4, 1.8, 2.5, 2.6, 2.8, 3.2, 3.6, 3.16(2,3,7,8,9), 5.3, 5.4, 5.8 Porter’s primary value chain activities Porter’s basic strategic positions Seven characteristics of useful information Five major transaction cycles The data processing cycle Budgeting Source data automation G/L, control accounts, subsidiary ledgers, General Journal Context diagram System flowchart Flowchart symbols Structured, semi-structured, unstructured decision making Data Flow Diagrams (DFDs) and symbols Database Management System (DBMS) and Relational DBMS Schemas and subschemas Database administrator Benefits of DBMS technology Batch processing Online real-time processing Primary key, foreign key, attributes, tuples Data warehouse Data Dictionary Data Definition Language (DDL) Data Manipulation Language (DML) Data Query Language (DQL) Update, Insertion, Delete Anomaly Entity integrity and Referential integrity The following questions are representative of the mc questions that will be on the exam. 1. Which of the following motivators would be a good indication of financial pressure that would contribute to employee fraud? a. a big change for the better in an employee's lifestyle b. an employee suddenly acquires lots of credit cards c. inadequate internal controls d. too close association with suppliers or customers 2. Research has shown that _______ accounts for the highest...
Words: 388 - Pages: 2
...Review Questions 7.4, 7.5, 7.6, and 7.8 only. (Hint: Write and test an SQL script, and then run the script. Save the script as DPB-e12-WPC-Create-Tables.sql for future use.) Do not run your answers to Review Questions 7.7 or 7.9! After the tables are created, run your answers to Review Questions 7.10 through 7.13. Note that after these four statements have been run the table structure is exactly the same as it was before you ran them. 7.17 Write INSERT statements to add the data shown in Figure 2-32 to the ASSIGNMENT table. Run these statements to populate the ASSIGNMENT table. (Hint: Write and test an SQL script, and then run the script. Save the script as DBP-e12-WPC-Insert-ASSIGNMENTData. sql for future use.) 7.26 Write one DELETE statement that will delete all data for project ‘2011 Q3 Product Plan’ and all of its rows in ASSIGNMENT. Do not run this statement. 7.30 What is an SQL view? What purposes do views serve? Why is this useful? 7.39 Explain how views can be used to improve data security. Chapter 7 SQL for Database...
Words: 462 - Pages: 2
...Database Design Larry Cohen DBM 380 James Cervi November 10, 2014 Database Design Database Systems A database is a structured group of data that is warehoused in a system. In order for a database to be functional it must be accessible and store large amounts of data. In order for databases to be reliable, in the event of a hardware failure, it must be easily recoverable. Also, the database must be flexible. Meaning, if there is something new that needs to be implemented, the database must be able to adapt to change. Database Architecture The database architecture is a set of parameters and processes that determine how data is stored and accessible by other individuals and databases. Database architecture focuses on the development, implementation, design, and maintenance of program. Design architecture helps develop software to better benefit the future user. There are numerous types of database that are created such as Oracle, SQL Server, and Microsoft Access. The SQL server is very important when it comes to database architects. Database architects use varies languages to create databases. Therefore, the languages are requested using the structured query language (SQL). IBM designed the SQL in 1974. It was first introduced by Oracle in 1979. SQL is very favorable among information technology specialist. Workplace Databases The database that my company uses is the Microsoft format. All salesman use Microsoft formatted handhelds to conduct orders and deliveries...
Words: 485 - Pages: 2
...Chapter 2 Data Models Chapter 2Data Models Answers to Review Questions 1. Discuss the importance of data modelling. A data model is a relatively simple representation, usually graphical, of a more complex real world object event. The data model’s main function is to help us understand the complexities of the real-world environment. The database designer uses data models to facilitate the interaction amongdesigners, application programmers, and end users. In short, a good data model is a communicationsdevice that helps eliminate (or at least substantially reduce) discrepancies between the databasedesign’s components and the real world data environment. The development of data models, bolstered by powerful database design tools, has made it possible to substantially diminish thedatabase design error potential. (Review Section 2.1 in detail.) 2. What is a business rule, and what is its purpose in data modelling? A business rule is a brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization’s environment. In a sense, business rules are misnamed: they apply to any organization -- a business, a government unit, a religious group, or a research laboratory; large or small -- that stores and uses data to generate information. Business rules are derived from a description of operations. As its name implies, a description of operations is a detailed narrative that describes the operational environment of an organization. Such a...
Words: 648 - Pages: 3
...Table of Contents How to create a database in Access. 3 How to create a table 5 How to make your Database useful. 7 How to Create a Relationship 12 How to Validate your Database 15 How to create a database in Access. Select Blank Database Name your database, for example, Cervi_Huffman_trucking. How to create a table Add tables by clicking on Create tables. Type in column names Repeat this process for every table that is needed. I prefer the design view it is easier to use. Remember to hit control S every once in a while. Yes, Access has an autosave but it does not always capture everything like the save command does. How to make your Database useful. A Database is built to answer questions (queries). Click on the Create tab. Select the type of query you want to write. In this case, I am going to select Simple Query Wizard. From Here you can select the table you want and the columns you want. You can select more then one table at a time. In other databases this is where you would write SQL. This will come in handy when you are testing the relationships you create in DBM 380. This will only work if you have established the relationships. (see the next Section). How to Create a Relationship Click on relationships. Select both tables. Click and drag the columns that need to be related. This allows you to identify which columns need to be related. Click on Create. This is what you will need to do for your...
Words: 324 - Pages: 2
...Query is a query which is created on the fly, for a specific Non-Generalizable one time requirement and may not be saved 2.3 What does SQL stand for, and what is SQL? SQL stands for structured query language. It is a special interfacing program that uses a certain language for sending questions to a search engine. 2.4 What does SKU stand for? What is an SKU? SKU stands for Stock Keeping Unit. A number assigned to a product by a retail store to identify the price, product options and manufacturer of the merchandise. 2.5 Summarize how data were altered and filtered in creating the Cape Codd data extraction. 2.6 Explain, in general terms, the relationships among the RETAIL_ORDER, ORDER_ITEM and SKU_DATA tables. 2.7 Summarize the background of SQL 2.8 What is SQL-92? How does it relate to the SQL statements in this chapter? 2.9 What features have been added to the SQL in versions subsequent to the SQL-92? 2.10 Why is SQL described as a data sublanguage? 2.11 What does DML stand for? What are DML statements? 2.12 What does DDL stand for? What are DDL statements? 2.13 What is the SQL SELECT/FROM/WHERE framework? 2.14 Explain how Microsoft Access uses SQL 2.15 Explain how enterprise-class DBMS products use SQL. 2.16 There is an intentional flaw in the design of the INVENTORY table used in these exercises. This flaw was purposely included in the INVENTORY tables so you can answer some of the following questions using only that table. Compare the SKU and INVENTORY tables...
Words: 448 - Pages: 2
...stored in the database to compare rate with other hospital. * Physicians can gather the relevant information quickly by providing the electronic medical record for each patient. * To find which programs and methods are best, create a historical record of the treatments. * Track the important measure to provide the data and information for example, normal length of patient wait, price for each day for a patient. 3. Hospital should carefully manage some of the cost and risk of using database because in a job market, there is short supply of skills such as database designer and database administrator. Without the specific faculty, it creates a risk to manage the database. The installation and administration expenses include updating software and installing of database and hardware. The translation cost includes the converting cost of older application into database environment. Instead of converting the hospital can choose new system. There is a need for the backup and recovery because the framework expenses are connected with those strategies. A hospital must predict the data administration cost and other activities cost related to data definition, ownership and maintenance. 6. Mountain View Hospital could use web based applications in a few ways. * Internet hospital personnel uses web based applications to create an intranet to access the databases. * Extranet application is used to third party billing with the insurance companies so hospital examines the...
Words: 410 - Pages: 2
...the attached Coding Standards. CRITICAL Resource: The attached Errors Table is a resource for dealing with error messages and other potential obstacles you may encounter writing code. Optional: Read the Week 2 and 3 Handouts in the Recommended Learning Activities to assist with creating this assignment. Optional: Complete the Week 2 and 3 Learning Activity Tutorials in the Recommended Learning Activities. REQUIRED: Use the Kudler Chart of Accounts PROVIDED. Analyze the "PROVIDED" Chart of Accounts spreadsheet in order to decide how you will design the database. Plan your Data Dictionary (You will be submitting data dictionary with your final project submission, Week 5.) REQUIRED: Use the Data Dictionary Template attached to create the Data Dictionary for the Learning Team Assignment. REQUIRED: Follow the instructions in the provided Excel Spreadsheet (Data Sheet) for the Kudler Chart of Accounts for creating the Excel Spreadsheet due this week. * Rename the Excel Sheet with the team name and Kudler Chart of Accounts (Example: Team A Kudler Chart of Accounts) ------------------------------------------------------------------------------- Assignment Request: Create the Team Database - using the...
Words: 1276 - Pages: 6
...| Database systems | Assignment # 1 | Areeba kamil (34421) | BSCS 4A | 10/4/2015 | 4.7. Consider the LIBRARY relational database schema shown in Figure 4.6. Choose the appropriate action (reject,cascade,set to NULL,set to default) for each referential integrity constraint, both for the deletion of a referenced tuple and for the update of a primary key attribute value in a referenced tuple.Justify your choices. CREATE DATABASE library; CREATE TABLE book( book_id int, title varchar(50), publisher_name varchar(50), primary key(book_id), CONSTRAINT "fk_book_publisher" FOREIGN KEY(publisher_name) REFRENCES publisher(name) ON DELETE SET NULL ON UPDATE CASCADE ); ON DELETE SET NULL is chosen in case the name of a publisher needs to be removed, it can be deleted and set to NULL in all the tables containing publisher’s name ON UPDATE CASCADE is chosen here so that all the tables containing that publisher’s name are updated in accordance to the new publisher’s name CREATE TABLE book_authors( book_id int, author_name varchar(50), CONSTRAINT "fk_book_authors_book" FOREIGN KEY(book_id) REFRENCES book(book_id) ON DELETE CASCADE ON UPDATE CASCADE ); ON DELETE CASCADE is chosen because in case a book is to be removed from the LIBRARY database, all the rows corresponding to that deleted book_id will be deleted from all tables containing book_id. Since this could be dangerous, we could have also used ON DELETE RESTRICT ON UPDATE CASCADE is chosen...
Words: 1569 - Pages: 7
...neighborhoods. EliteVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie “Twist in the Wind”. “Twist in the Wind” would be one MOVIE and each copy would be a VIDEO. A rental transaction (RENTAL) involves one or more videos being rented to a member (MEMBERSHIP). A video can be rented many times over its lifetime, therefore, there is a M:N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is provided in Figure P7.65. 65. Write the SQL code to create the table structures for the entities shown in Figure P7.65. The structures should contain the attributes specified in the ERD. Use data types that would be appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD. MEMBERSHIP Mem_ Num Mem_ Fname Mem_ Lname Mem_Street Mem_City Mem_ State Mem_Zip Mem_ Balance RENTAL Rent_Num Rent_Date Mem_Num DETAILRENTAL Rent_Num Vid_Num Detail_Fee Detail_Duedate Detail_Returndate Detail_Dailylatefee VIDEO Vid_Num Vid_Indate Movie_Num MOVIE Movie_Num Movie_Name Movie_Year Movie_Cost Movie_Genre Price_Code PRICE Price_Code Price_Description Price_Rentfee Price_Dailylatefee 68. Write the SQL command to change the movie year for movie number 1245 to 2010. UPDATE Movie SET MOVIE_YEAR = 2010 WHERE MOVIE_NUM = 1245; 69. Write the SQL command...
Words: 344 - Pages: 2
...Chapter 2 - Database System Concepts and Architecture Data Models, Schemas and Instances A characteristic of the database approach is that it provides a level of data abstraction, by hiding details of data storage that are not needed by most users. A data model is a collection of concepts that can be used to describe the structure of a database. The model provides the necessary means to achieve the abstraction. The structure of a database is characterized by data types, relationships, and constraints that hold for the data. Models also include a set of operations for specifying retrievals and updates. Data models are changing to include concepts to specify the behaviour of the database application. This allows designers to specify a set of user defined operations that are allowed. Categories of Data Models Data models can be categorized in multiple ways. • High level/conceptual data models – provide concepts close to the way users perceive the data. • Physical data models – provide concepts that describe the details of how data is stored in the computer. These concepts are generally meant for the specialist, and not the end user. • Representational data models – provide concepts that may be understood by the end user but not far removed from the way data is organized. Conceptual data models use concepts such as entities, attributes and relationships. • Entity – represents a real world object or concept • Attribute - represents...
Words: 2961 - Pages: 12
...LABEL • B. MODIFY COMMAND • C. MODIFY STRUCTURE • D. MODIFY REPORT Answer: Option C 4.Which command is used to set a link between two database files • A. JOIN • B. UPDATE • C. BROWSE • D. SET RELATION Answer: Option D 5.The distinguishable parts of a record are called • A. files • B. data • C. fields • D. formats Answer: Option C 6.Related fields in a data base are grouped to form • A. data file • B. data record • C. menu • D. bank Answer: Option B 7.Data item characteristics that are important in data management include • A. punctuation • B. language • C. spelling • D. width Answer: Option D 8.Data dictionary tell DBMS • A. what files are in the database • B. what attributes are processed by data • C. what these files contain • D. all of above 9.Data management system may be implemented as • A. system software • B. application software • C. computer program • D. all of above 10.RAID configurations of a disk are used to provide • A. fault tolerance • B. high speed • C. high data density • D. none of above 11.Which normal form is considered adequate for normal relational database design • A. 2NF • B. 5NF • C. 3NF • D. 4NF 12. Which is a type of DBMS software • A. utilities • B. query language...
Words: 1983 - Pages: 8
...You have been hired by the Venerable International Network of Oenology (VINO) and tasked to assist in computerizing the management of information about its members’ data, as well as the information they collect about several wines. This oenological organization is quite large and hence has many members of the same name. To distinguish among members, they issue membership cards, each with a unique member number. The contact details of members are also book-kept so as to facilitate the mailing of event announcements by post and/or email and calls for meetings. Once a week, VINO organizes a tasting session. In those sessions, several bottles are offered for tasting, and each member records their evaluation for each bottle they taste (outstanding, good, mediocre, bad). Such evaluation will differ between one taster and another for the same wine. Besides, the actual quality and therefore the evaluation will vary among bottles of a given wine. Any bottle opened during a tasting session is consumed during that session. Wines are identified by their name (“Agiorgitiko”), appellation (“Corinth”), and vintage (1993). Other data of interest about a wine is the alcohol degree (13.5), and its country of origin (“Montenegro”), and grape variety (“Merlot”). At any given time, there will be several bottles of the same wine in the cellar. For each such wine, the bottles are numbered. For instance, the cellar may store 15 bottles numbered 1 to 15 of a Georgian wine...
Words: 398 - Pages: 2
...one or work with one. But, first, let’s examine why this is valuable to us and to our organizations. Organizing Data and Information…now that is a challenge for us today given the overwhelming amount of data available today. One could read the title of this chapter and not anticipate that the theory of database design and management would be included. Some authors consider this to be a key element and have clearly indicated the role and influence of database theory and practice is essential. A well-managed organization and IS department can be compared with a well-designed and functioning database. [pic] Source: http://www.datawarehouse4u.info/images/data_warehouse_architecture.jpg The effort this week will not attempt to present the entirety of database analysis, design, maintenance, and management, but will present an overview of it. For those of you who are not experienced or expert in any programming languages, our treatment will be a huge sigh of relief, because we will not create any databases. We will leave that for those other courses. But I would ask that everyone post as part of their notes how much experience you have in creating databases (and if you don’t have any, what you’d like in a database that would be created for you). With the explosion of data, the challenge already exists of accessibility, interpreting, and changing DATA into INFORMATION. Once we have accomplished this feat, we can then move onto the next stage and that is KNOWLEDGE....
Words: 382 - Pages: 2
