Reem Ali-FWA- Part2.pdf

‫‪-Reem Ali-‬‬ ‫‪CIS1303‬‬ ‫‪DATA AND INFORMATION‬‬ ‫‪MANAGEMENT‬‬ ‫دعاء ما قبل االمتحان ‪‬‬ ‫اللهن ٌ...

‫‪-Reem Ali-‬‬ ‫‪CIS1303‬‬ ‫‪DATA AND INFORMATION‬‬ ‫‪MANAGEMENT‬‬ ‫دعاء ما قبل االمتحان ‪‬‬ ‫اللهن ٌب هعلّن هىسى علّوًٌ‪ ،‬وٌب هفهن سلٍوبى ف ّهوًٌ‪ ،‬وٌب هؤتً لقوبى الحنوخ وفصل الخطبة آتًٌ الحنوخ وفصل الخطبة‪ ،‬اللهن اجعل ألستٌب عبهشح ثزمشك‪ ،‬وقلىثٌب ثخشٍتل‪ ،‬وأسشاسًب ثطبعتل‪ ،‬إًل على مل‬ ‫شًء قذٌش‪ ،‬حسجٌب هللا وًعن الىمٍل‪.‬‬ ‫عند التوجّه إلى االمتحان ‪‬‬ ‫اللهن إًً تىملت علٍل وفىضت أهشي إلٍل ال هلجأ وال هٌجى إال إلٍل‪.‬سةّ أدخلًٌ هذخل صذق وأخشجًٌ هخشج صذق‪ ،‬واجعل لً هي لذًل سلطبًب ًصٍشا‪.‬سةّ اشش لً صذسي وٌسش لً أهشي واحلل‬ ‫عقذح هي لسبًً ٌفقهىا قىلً‪ ،‬ثسن هللا الفتّب ‪ ،‬اللهن ال سهل إال هب جعلته سهال وأًت تجعل الحزى إرا شئت سهال ٌب أسحن الشاحوٍي‪.‬اللهن افتح عل َّ‬ ‫ً فتى عجبدك العبسفٍي‪ ،‬اللهن اًقلًٌ هي حىلً وقىتً وحفظً‬ ‫إلى حىلل وقىتل وحفظل‪ ،‬اللهن اجعل لً هي لذًل سلطبًب ًصٍشا‪.‬‬ ‫أثناء االمتحان ‪‬‬ ‫ال إله إال أًت سجحبًل إًً مٌت هي الظبلوٍي‪ٌ ،‬ب حً‪ٌ ،‬ب قٍىم ثشحوتل استغٍث‪ ،‬سةّ إًًّ هسًٌّ الضش وأًت أسحن الشاحوٍي‪.‬سةّ اشش لً صذسي وٌسّش لً أهشي‪ ،‬واحلل عقذح هي لسبًً ٌفقه قىلً‪.‬‬ ‫ثسن هللا الفتب ‪ ،‬اللهن ال سهل إال هب جعلته سهال‪ ،‬وأًت تجعل الحزى إرا شئت سهال‪.‬‬ ‫‪ ‬عند تعسر االمتحان‬ ‫ٌىم ال سٌت فٍه سدّ إل ّ‬ ‫ً حبجتً وأًت أسحن الشاحوٍي‪.‬‬ ‫الله ّن ٌب جبهع الٌبس إلى ٍ‬ what is DataDescription of things that are recorded, classified, and stored but not organized to give any meaning where can I find data (numbers/letters/images) A single piece of data is a single fact about something that interests us Give an example of data that can be around us : 1-emails that are stored in our email app 2-photos are on Instagram 3-videos can be stored in YouTube 4-contacts and their phone numbers are on our phone What do you mean data is the most important corporate resource? Corporate: Means business or organization or company Resource meansSomething important What is information? Data that have been organized or processed so that they have meaning and value to the person who receives it. What is knowledge consists of information that results in an understanding KnowledgeAn example when survey data/ information are analyzed to make a decision. Getting any result out of our data using sum, count, max, min is  knowledge Data Cycle Example: A survey collects facts This is Data When information is analyzedThis is now Knowledge When data is processed This is now Information What are the two sources of data? Explain with example.  Internal source of data: day-to-day business activities reside within the organization. (HR/sales/marketing/customer information) (easy to obtain and manage)  External source of data: Business activities reside outside the organization such as government data (census/chamber of commerce), nongovernment data (news/media/annual reports) and international organizations (IMF/united nations data) Data that is outside an organization and need to search on it to get? External Data Provides an interface between the user and the dataAre DBA(database administrator) What is a DATABASE? A database is a collection of data that is organized in such a way that it can be easily accessed, modified, and protected what is The purpose of storing the data into the database It can be easily accessed, modified, protected The collection of related tables Database How to create a database? we need a system to manage the database. This system is called a DBMS DBMS is the acronym ofDatabase Management System What is a Database management system (DBMS)? A DBMS is a The software that is used to organize data.  It performs operations like Storing, retrieval or modification on the database for user.  It is software which is used for management (Storing, searching, updating, deleting of data) of database.  It is interface between database and user of the database. What is interface: Something in between two things Data resides within an organization and easy to getDBMS MS Access, Oracle, Sql server and DB2 are DBMS software What are the three types of Database Users? Explain each of them: 1- End Users: The person who will be using the database, they are not computer professionals but are trained database operators. 2- Developers/Programmers: The computer professional who design, create programs for end users. 3- Database Administrator (DBA): Responsible for the management and maintenance of the database on a day-to-day basis. The person who is not a professional and will be using the database What are the disadvantages of File Processing System? Answer: File Processing system has disadvantages like: Data redundancy  Data is stored more than once so will occupy more space Data inconsistency   Data is stored more than once may be lead to errors in the data Unsecure data  May be face security issue What is Repeated Data? When a data is stored more than once we say repeated data e.g. Emp# is repeated and there are some other columns as well. Repeated data has two types  Duplicate  Redundant Remember: in both type data is repeated  Duplicated data: A data value is considered to be duplicated when loss of information occurs after removing the repeated data field.  A data value is considered redundant if one value can be removed without the loss of information. We cannot do anything with duplicate. However, we can reduce or minimize the redundancy. what does it mean data redundancy? Refers to the same fact about the business environment being stored more than once within an information system. How we can reduce the redundancy? we split the table into two tables to reduce the redundancy. Data Redundancy leads to: Inaccurate data occurs when a Inconsistent datadata that looks field is filled but the information is different but represents the same not correct thing What is data security? How DBMS achieve data security? Data Security refers to the prevention of access to the data by unauthorized users. The DBMS includes security features such as password protection, encryption, permissions, …etc. What is data concurrency? means accessing the same data at the same time by multiple users. What are the 4 steps of Data Life Cycle? 1) Collect Data: Gather and store data 2) Process Data: Where the data is entered into the database in a format the database can work with 3) Analyze Data: Extracting the value from data, both through the use of statistical analyses techniques 4) Disseminating Data: is distributed to stakeholders What is the disadvantage of storing the data on the paper?  Huge amounts of paper needed  Only one person at a time can use a given document  Searching for data requires a long time  Paper documents can easily be lost or damaged  Data Accuracy Issues What is the advantage of storing data in the database? 1. Encourages data sharing 2. Reduces data redundancy (Less duplicate data entry) 3. Ensures data accuracy 4. Permits storage of vast volumes of data with acceptable access speed. 5. Allows data querying (Easier and faster to find data) 6. Provides tools to control: a. data security b. data privacy c. backup and recovery What are the security issues? How the security can be breached? Security issues involve protecting company data from: 7. theft 8. malicious 9. phony changes 10. e.g. someone trying to increase his/her own bank account balance. Data can be breached by the following methods: 1. Stealing Disks or Computers 2. Computer Viruses 3. Damaging Computer Hardware What is backup? What are the 2 conditions for backup? Data backup is one of the most effective ways of protection against the loss of important data. Two conditions for backup: i. A database must be backed-up on a regularly scheduled basis. (daily/weekly/monthly) ii. The backup set must be stored in a safe place, preferably away from the original set. Explain recovery and when do we do recovery process? Data recovery is the process of reconstructing data from the backup set when it cannot be accessed normally (most likely due to data corruption or loss). We recover data: following a hardware failure following a natural disaster Database models Hierarchical Model In the Hierarchical model, data is organized into a tree-like structure with a single root Network Model (N – Model) This is an extension to Hierarchical Model where data is organized like a graph and more than one Object Oriented Model (R – Model) In Object Oriented Data Model, data and their relationships are contained in a single structure which is referred as object in this data model. Relational Model (R – Model) It’s primary and widely used database model for data storage and processing where data is stored in two-dimensional tables and the relationship maintained by storing a common field. What is Logical data model: representation of an organization’s data organized in term of entities, attributes and relationship. (helps understand how the data functions without being concerned about how it will be implemented) What is Physical data model: represents how the data model will be implemented/built. (must show complete database with datatypes/columns/attributes/relationships) Data Model is a diagram that displays a set of entities and the relationships between them  Attributes are classified as one of the following: Mandatory (nulls are not allowed), indicated by * Optional (nulls are allowed), indicated by a lowercase o What are The attribute whose value cannot be nullMandatory What are the definition of data relationship :A data relationship is a natural association that exists between two or more entities to enforce Business Rules or Organizational Policies Different kinds: Unary relationships: Link an entity to itself They are A kind of entity Binary relationships : Link 2 entities relationship Ternary relationships: Link 3 entities What are The meaningful association between entity types Relationship A data relationship has two perspectives, what it must be? labelled, cardinality and modality What dose mean CARDINALITY ? CARDINALITY, which defines the number of MAXIMUM occurrences of one entity for a single occurrence of the related entity Represents the maximum number of entities samples that can be involved in a particular relationshipCARDINALITY MODALITY defines the number of MINIMUM occurrences of one entity for a single occurrence of the related entity MODALITY values can be 0 which means that the relationship is optional, This is represented by a dashed line ------------------------ or 1 which means that the relationship is mandatory , This is represented by a solid line ______________________________________ Define bit, field and record. BIT: data represented in binary digit format (0 or 1) Field: the column, representing the fact (multiple bits) Record: the row of a table containing multiple fields What is primary key? Explain unique values. The primary key uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only one primary key. Unique values mean these values are not repeated in the same table. What is entity integrity? Entity Integrity means each database table must have a column or combination of columns with unique values. What is foreign key? Can foreign key values be repeated? A foreign key is a field (or collection of fields) in one table that refers to the primary key in another table. Foreign key values can be repeated. What is entity, attributes and relationship? Explain with help of example. Entity: a “thing” or “object” in our environment that we want to keep track of or study. Attribute: a fact, property, characteristic that describes the behavior of an entity. Relationship: a connection, link, join that relates two entities together. Practice question for clo 1& 2 Binary Relationship between entities may be: One-to-One Binary Relationship One-toMany Binary Relationship Many-to-Many Binary Relationship A single occurrence of one entity type can be associated with a single occurrence of the other entity type and vice versaOne-to-One The relationship between the salesperson and the customersone-to-many The minimum number of entity occurrences that can be involved in a relationshipModality Refers to the accuracy of the data and must meet constraints or conditionsData integrity The ability to modify the physical schema without changing the application programsData independence To allow several users can have access to the same data and still use it in a variety of waysConcurrency A kind of entity relationship: Unary \\ Binary \\ Ternary The ability to tie together pieces of related data within an information systemData integration The column of the table representsAttribute The row of a table representsRecord Helps to ensure the security of data: Passwords Encryption Group permissions The solution to eliminate data redundancy problemSplit table What are the three entity integrity rules in a Relational Database Model? 1-Every table must have a Primary Key 2-The column(s) chosen for the Primary Key must be unique 3-The column(s) chosen for the Primary Key must not be NULL A single piece of data is _____ the business environmenta fact about A name, age and a PO Box are all examples ofData Data Backup and recovery meansAbility to reconstruct data if it is lost or corrupted, say in a hardware failure All Data Backup and recovery meansAbility to reconstruct data if it is lost or corrupted, say in a hardware failure. Protecting against hackers is primarily a matter of computer security If you want to prevent unauthorized users access to a database system, this is referred to as data Security Data redundancy refers to The same data unnecessarily being kept more than once within an information system Rebuilding a database after it is destroyed by fire or flooding is an example of backup and recovery Which of the following is NOT a medium for backing up digital dataPaper Which of the following is represented by a row in a tableA record A foreign key mustMatch the field value of a primary key in a related table. A primary keyMust be unique Another name for a record is aTuple When creating ER diagrams the correct format/way to type the entity isSALESPERSON Relationships between two entities is referred to asBinary An ER Diagram with two entities in a many-to-many relationship converts toThree relational tables A relationship involving only one entity is referred to asUnary The collection, processing, analyzing and disseminating of information is referred to asData Life Cycle Entity integrity means:Each table must have a PK Referential integrity meansFK values must match PK values in a related table A construction company stores large volumes of data about their employees and projects. All their data is stored in paper and Excel files. It takes a long time for employees to find information and prepare reports. This issue relates to difficulties in managing data A database administrator or a DBA is responsible for managing the daily operations of a database including backup and recovery A single piece of data is a fact about the business environment. An employee is checking data stored in his company’s database. He finds that the same employee data is stored in three tables to be used by different departments. This is an issue relating to data  redundancy An employee who works in the marketing department of a company should not be able to make changes to personal information of colleagues working in other departments. This is an issue relating to  data privacy At the end of each semester in a school, student assessment data and grades on the database are copied to a different server. This is an example of  data backup Data and information that have been organized and processed to convey understanding and experience and is later used to take actions is consideredKnowledge Data that is collected during normal business activities of a company such as sales, marketing, customer registration and hiring of employees is considered to be internal data Data that is stored more than once in the same database could lead to  inconsistency In an E-R Model a person, place, or thing with characteristics to be stored in the database are referred to as?  entity Select the best description for Referential integrityForeign key values must match primary key values in a related table The DBMS software enables users to delete, update, add and retrieve data The collection, processing, analyzing and disseminating of information is referred to asData Life Cycle The computer professional responsible for designing creating database forms and interfaces for end users are known asdatabase developers or programmers When a table is created without a primary key, data integrity is lost. When creating ER diagrams the correct format/way to name the entity isSALESPERSON Which of the following is used to uniquely identify each record?  primary key Security is related to the prevention of access to a database by unauthorized users One-to-One STUDENT COURS  STUDENT(……(PK),……..,…….,…….(FK)) ) FK ( ‫انا اختار وين احط‬ COURS(……(PK),……..,…….,…….) One-to-Many STUDENT COURS  STUDENT(……(PK),……..,…….,…….(FK)) Many ‫ ) صوب‬FK ( ‫احط‬ COURS(……(PK),……..,…….,…….) Many-to-Many STUDENT COURS  STUDENT(……(PK),……..,…….,…….) ) FK , PK ( ‫اسوي تيبل يديد اذا قال ار دي ام نحط‬ COURS(……(PK),……..,…….,…….) ‫ اي ار دي ف اسوي العادي مب نفس الي فوق‬/ ‫اما اذا قال أنتتي‬ STUDENT_COURS(…..…(PK)(FK),………(PK)(FK)) Map the following ERD to a relational data model using the mapping rules: Converting 1:1  3 options: Option 1: Option 2:  Converting 1:M [P.1.F.M] Primary key in the One becomes Foreign key in the Many Converting M:N [Associative entity]: Exam question: BORROWER (bor_Id PK, bor_IsStudent, bor_IsFaculty, bor_NumberOfLoans) BOOK (book_Id PK, book_title, book_author, book_isdn) LOAN (loan_Id PK, book_Id FK, bor_id FK, loan_date) Map the following RDM to a relational data model using the mapping rules: Use page 11 !!!!!! 1) EMPLOYEE(ID(pk),Name) ABSENCE(ID(fk),Abs_number(pk),Abs_date) 2) SALESPERSON (Salesperson_Number(pk), Salesperson_Name, Commission_percentage, Year_of_Hire) CUSTOMER(Customer_Number(pk),Customer_Name, HQ_City) CUSTOMER_SALESPERSON(Customer_Number(pk)(fk), Salesperson_Number(pk)(fk)) 3) STUDENT (Student_ID (pk),Student_Name,Student_address,Seat_Number(pk)) SEAT (Seat_Number(pk),Seat_location) ERD 1. Relational Model (Tables) for above ERD. PROJECT (BuildingID (PK), BuildingName, Description, Location) WORKER (WorkerID (PK), WorkerName, WorkerAddress, SupervisorID (FK)) PROJECT_WORKER CAR (RegisterationNumber (PK), CarName, Model, WorkerID (FK)) #*BuildingID SUPERVISOR (SupervisorID (PK), SupervisorName, Address) #* WorkerID PROJECT_WORKER (ProjectID (PK, FK), WorkerID (PK, FK)) Normalization 1st Normal Form: Sometimes we have columns which have multiple values roll_no name subject 101 Akon OS, CN 103 Ckon Java 102 Bkon C, C++ How to solve it? 1ST Normal Form: There should be no Column which have multiple values roll_no name subject 101 Akon OS 101 Akon CN 103 Ckon Java 102 Bkon C 102 Bkon C++ 2nd Normal Form: It should be in 1st NF+ There should be no Partial dependency. Dependency: Name of the student having ID 10 student_id name reg_no branch address 10 Ahmed 07-WY CSE Kerala 11 Ahmed 08-WY IT Gujarat What is partial dependency? Primary Key: An attribute or combination of attribute which is unique and cannot be NULL. Composite Key: That primary key which has more than one attribute. student_id subject_id marks 10 1 70 10 2 75 11 1 80 subject_id teacher 1 Java Teacher 2 C++ Teacher When non key field or attribute depends upon part of the primary key we say partial dependency. In the above table teacher only depends upon subject_id. If you want that your table should be in 2nd Normal Form there should be no partial dependency. subject_id subject_name teacher 1 Java Java Teacher 2 C++ C++ Teacher 3 Php Php Teacher 3rd Normal Form: 2nd NF + there should be no Transitive dependency: Transitive dependency: When two non-key attribute depends upon each other Total_marks depends upon exam_name student_id subject_id marks exam_name H0012234 CIS 1302 78 FWA H00654321 CIS 1403 9 Quiz 1 H0098765 CIS 2403 24 Practical exam_name total_marks FWA 100 Quiz 1 10 Practical 25 Make another table for exam_name and total_marks to remove transitive dependency.  Data Definition Language (DDL): 1) Create the PASSENGER table as per the given structure and design. CREATE TABLE Passenger ( Passenger_ID NUMBER(8), Passenger_Name VARCHAR2(20), Budget NUMBER(9, 2), Gender CHAR(1) NOT NULL, Phone VARCHAR2(15), Date_of_Birth DATE NOT NULL ); 2) Create the TICKET table as per the given structure and design. CREATE TABLE Ticket ( Ticket_Number NUMBER(10), Travel_date DATE, Airline_Name VARCHAR2(15), Price NUMBER(9,2), Tax NUMBER(9,2), Passenger_ID NUMBER(8) );  3) 3) Set the primary key for the PASSENGER table. ALTER TABLE Passenger ADD CONSTRAINT pk_passid PRIMARY KEY(Passenger_ID); 4) Set the primary key for the TICKET table. ALTER TABLE Ticket ADD CONSTRAINT pk_tcknum PRIMARY KEY (Ticket_Number); 5) Set the PASSENGER_ID as the foreign key for the TICKET table as per the given design. ALTER TABLE Ticket ADD CONSTRAINT fk_pid FOREIGN KEY (Passenger_ID) REFERENCES Passengers (Passenger_ID); 6) Write the SQL statement required to add a column to the PASSENGER table. The new column should be named CITY and hold 30 letters. ALTER TABLE PASSENGER ADD (CITY VARCHAR2 (30)); 7) Write the SQL statement required to reduce the size of the Airline_Name column so that the column holds 12 characters. ALTER TABLE PASSENGER MODIFY (Airline_Name CHAR(12)); 8) Write the SQL statement(s) required to add a constraint to GENDER column to only accept values M or F in the PASSENGER table. ALTER TABLE Passengers ADD CONSTRAINT ck_gender CHECK ( Gender IN ('M', 'F') ); 9) Write the SQL statement(s) required to change the TICKET table name to PASSENGER_TICKET. RENAME TICKET TO PASSENGER_TICKET; 10) Write the SQL statement(s) required to set the PRICE column in TICKET table, so the column will only accept values between 2000 and 5000. ALTER TABLE PASSENGER ADD CONSTRAINT PRICE_CHCK CHECK(PRICE >=2000 AND 5000);  11) Write the SQL statement(s) required to make sure that the PHONE column in the PASSENGER Table has a value (column cannot be NULL). ALTER TABLE PASSENGER MODIFY PHONE CONSTRAINT NOT_NULL_PHONE NOT NULL; 12) Write the SQL statement required to INSERT the following record in the TICKET table. INSERT INTO Ticket VALUES (5454, 'DEC-13-2012', 'Etihad', 1600.75, 120.50, 4442200); 13) Write the SQL statement required to INSERT the following record in the PASSENGER table. INSERT INTO Passenger VALUES ( 4442200, 'Issam Hamdan', 15000, 'M', '0504343323', 'JAN/23/1979', 'Ras Al Khaimah' );  Data Manipulation Language (DML): 1) Display the name, date of birth and salary of any professional forwards that were born between 1970 and 1975. SELECT Player_NAME, BIRTHDATE, SALARY FROM PLAYER WHERE POSITION='Forward' and STATUS = 'Professional' and BIRTHDATE BETWEEN 'Jan-01-1970' and 'Dec-31-1975 2) Display the names, date of birth, salary and bonus for all players. The bonus is calculated as 0.25 * SALARY. Display only those players whose salary is not null. The output should list the highest bonus first. Re-label the last column “Player Bonus”. SELECT Player_Name, BIRTHDATE, SALARY, SALARY * 0.25 "Player Bonus" FROM PLAYER WHERE SALARY is not null ORDER BY "Player Bonus" desc' 3) List all information for players who live in Al Ain and whose salary is greater than 5000. SELECT * FROM PLAYER WHERE CITY = 'Al Ain' and SALARY > 5000  4) List all information for players who were born before 1973. SELECT * FROM PLAYER WHERE BIRTHDATE < 'Jan-01-1973' 5) 5) List all information for players whose name starts with the letter “A”. SELECT * FROM PLAYER WHERE Player_NAME like 'A%' 6) List all information for players whose number is between 5 and 10. SELECT * FROM PLAYER WHERE Player_ID BETWEEN 5 and 10) 7) Write a Query to display the maximum SALARY, minimum SALARY and average SALARY in the PLAYER table. Change the title to ‘Highest SALARY’, ‘Lowest SALARY’ and 'Average SALARY' respectively. SELECT MAX(SALARY) AS "MAX SALARY", MIN(SALARY) AS "MIN PRICE", AVG(SALARY) "AVG SALARY" FROM PLAYER; 8) Write the SQL statement required to UPDATE the player salary from 2500 to 5000. UPDATE PLAYER SET Salary = 5000 WHERE Salary = 2500;