ICT

Database

Data types

• boolean
• number(integer, float) cannot store number starts with 0
• string(Text)
• Date/Time

Structure

• Database
  
  • Tables
    
    • Records(row)
      
      • Data

Concepts

• Input masks:
  
  • prevent users from inputting invalid data
  • make data entry easier by controlling of input values
• Key field (Primary key):
  
  • uniquely identifies each record in a table
• Sorting:
  
  • rearrange all the records in ascending/descending order according to specific records.
• Filtering:
  
  • extract a subset of records in a table according to specific criteria

SQL Syntax

SELECT field1, field2, ...
FROM table_name

For fields, * can be used for all fields

Where

Condition, used for filtering records.

SELECT field1, field2, ...
FROM table_name
where condition

Operators:

Operator	Example
=	str = 'TEXT' num = 1
<>	Num <> 1
> < >= <=	num > 1
LIKE	str LIKE 'ab_d%'
IN	str IN ('a', 'b', 'c')
AND OR NOT	num < 10 OR num > 100

For LIKE, _ stands for 1 character, while % stands for 0+ characters.

For IN, the set after it can be another SELECT command.

GROUP BY

SELECT column_name(s)
FROM table_name
GROUP BY column_name(s)

Group the rows with the same column value into groups.

Order by

SELECT field1, field2, ...
FROM table_name
ORDER BY field1, field2,... ASC|DESC

• ASC: ascending
• DESC: descending

If field1 equals, check field2. If all equal, use the order stored in the table

Functions

• MIN: smallest value of the specified column of the selected rows
• MAX: largest value of the specified column of the selected rows
• COUNT:
  Number of rows that matches the specified criteria
  Note that if used with GROUP BY, COUNT will return the number of rows of the group
• AVG: return the average value of the specified column of the selected rows
• SUM: return the total value of the specified column of the selected rows

Note that if used with GROUP BY, these function will process the rows in each group instead of all the rows at once.

Integrated use of a software suite

Concepts

• OLE
  
  • Allows data exchange between two applications
  • Allows the integration of functions of two applications
• Object linking: link to a source file at a certain path.
  
  • The size is relatively smaller than using object embedding.
  • Any changes made in the source file will affect the document accordingly.
  • When the location of the filed has changed, the path has to be changed either, or there will be broken link.
  • Need the specific software to view/edit the object.
• Object embedding: Store the entire file(object) in the document.
  
  • The size will be relatively larger than using object linking
  • No changes can be made to the object unless changing the document itself.
• Mail merge: Integrates the structured data source to a document template and produces multiple documents.
  
  1. Specify the document type of the template
  2. Select the starting document
  3. Choose the recipients list to be merged
  4. Insert the merge fields into the starting document
  5. Preview the merged letters and edit the recipients list or exclude specific recipients if necessary
• software suite: a group of software applications designed to work together

Networking

Concepts

• LAN: Small area network
• WAN: Large area network, e.g.: Internet
• Peer-to-peer: without a server, can communicate directly with each other.
  
  • Low performance
  • Low cost, easy to setup, flexible
• Client/Server: Connected to a central server.
  
  • Higher cost
  • Better security control and network service
• Quality information
  
  • Completeness
  • Accuracy
  • Authority
  • Objectivity
  • Timeliness
  • Legality
• Reducing file size:
  
  • increase compression ratio
  • Lower frame rate
  • Lower frame size
  • lower quality sound track or stereo to mono
• Plugin: small program that integrate into a web browser to provide additional functionality
  
  • Keep the browser efficient and small size, as only necessary plug-ins are included
  • Keep the browser up-to-date to handle different elements
• E-commerce:
  
  • Seller
    
    • Advantage
      
      • Access to worldwide market
      • Minimal sale cost
      • Able to analyze the purchasing habits of customers, and provide a tailored(custom made) recommendation
    • Disadvantage
      
      • Unable to interact face-to-face with customer
      • Difficult to promote to traditional customers
  • Buyer
    
    • Advantage
      
      • Can find hard-to-get items with minimum effort and cost
      • Automated cost-comparison
    • Disadvantage
      
      • Cost for shipping and wait for delivery
      • Cannot see or feel the product before purchasing
      • Difficult to return the defective item to the seller (Not satisfy with the product)
• E-government
  
  • Advantage
    
    • round-the-clock service
    • Faster and more up-to-date information
    • save travelling and queuing time
    • reduce administrative cost
    • improve service efficiency and accuracy
  • Limitation
    
    • Some citizen may not have access to the Internet
    • Some service require person-to-person interaction
• E-learning
  
  • ’Just-in-time’ knowledge acquisition (Learn what you need)
  • consistent course delivery
  • can be customized to fit the need of an individual
  • modular and flexible
  • allow immediate assessment and feedback
• Website buidling
  
  • Categorize the content
  • Avoiding lengthy pages
  • Keeping a uniform design
  • Creating hyperlinks for navigation
  • Opening a webpage in a reasonable time
  • keep updated
• Website testing
  
  • Including broken hyperlinks
  • Missing hyperlinks
  • Including outdated files
  • Requiring a long time to download a file

Hardware

• Network interface card: connecting a computer to a network.
  
  • Media access control (MAC) address: unique address for each NIC to identify the computer.
  • Connectors: BNC: round, RJ-45: rectangle
  • bandwidth: measured in bits per second
• Switch and Hub: central connection for cables on a network. Switch can connect more cables and its efficiency is higher than hub.
• Repeater: amplifies received signals for long distance transmission.
• Bridge: interconnect LANs of the same type.
• Router: interconnect LANs of same/different type
• Modems:
  
  • Dial-up modem: Convert digital signals into/from analogue signals for transmission using telephone lines. MAX speed: 56Kbps.
  • Cable modem: Similar to dial-up modem, but for cable TV line.
• Cables:
  
  • Twisted pair: use RJ-45 connector. Widely used.
    
    • Shielded: has a metal wrapper to reduce noise and EM interference
    • Unshielded: cheaper
  • Coaxial: for cable TV. Use BNC connectors. Can retain strength of the signal over a long distance. Can transmit more information than twisted-pair cables.
  • Optical fibre: use glass threads to transmit light signal.
    
    • High capacity,
    • fast transmission speed,
    • low noise nor interference from other devices,
    • thinner, lighter, more durable.
• Wireless
  
  • Satellite: slow speed, expensive
  • Microwave: must have a line-of-sight to the access point.
  • Wi-Fi: widely used.
    
    • cost of building up is lower
    • network structure can be modified
    • can be expanded to outdoor areas
    • unstable(affected by surrounding environment)
    • risk intrusion(security)
    • coverage of the access point is limited
    • cost of building up a large wireless network is high
• Connections:
  
  • Broadband: higher transmission speed
    
    • ADSL: lower upload speed than download speed
    • SDSL: same upload speed and download speed. Used for companies
    • Cable modem: slow
  • T-carrier: direct connection between ISP and the server
    
    • allow simultaneous network access
    • very expensive
    • more secure, as no intermediate nodes

Protocol

• TCP:
  
  • Transport layer
  • Divide file into packets, number them and forward one by one to the IP layer
  • Reassemble the received data packets into the original file
• IP:
  
  • Network layer
  • Adding destination IP address to packets for routing
  • Packet fragmentation (Further divide the packet) as maximum packet size allowed on some network may be smaller than the original packet
  • Select the path which require the shortest time to the destination.
• FTP
  
  • transfer files, need a server and a client
  • require user authentication, or using anonymous FTP
  • ASCII mode and binary mode
• Email
  
  • SMTP: mail server to mail server.
    
    • Push mails to receiver server, receiver is not allowed to request the sender server.
    • Unable to authenticate e-mail sender
    • Not efficient in sending mails with binary file attachments
  • POP: server to client
    
    • download mail from the server to the client, and delete that from the server
    • small capacity
    • can read and manage downloaded e-mails at any time without Internet connection.
  • IMAP: server to client
    
    • Stores all read and unread e-mails in the server
    • capacity is higher
    • more than 1 user can access the same mail box at the same time
    • support partial fetching(Leaving part unread, such as the attachment)
  • Structure
    
    • CC: duplicated copies being sent to other recipients who are publicly informed
    • BCC: duplicated copies being sent to other recipients who are privately informed. Invisible from other receivers.

IP Class

Class Number	Class	Network address	Host address	Number of host
0	A	1 byte	2-4 byte	16,777,217
10	B	1-2 byte	3-4 byte	65,534
110	C	1-3 byte	4 byte	254

Class number is the bits at the beginning of the IP.
For network address, class number need to be removed
Host address of all 0s and 1s are reserved.

Domain name

• DNS server is used to convert domain name into IP. (Process: name resolution)
• Top level domain: .com, .edu, .gov, ...
• Country code: .us, .hk, .cn, .tw, ...

Uniform resource locator (URL)

Protocol://DomainName/path:port
Ex: https://apis.google.com/js/rpc.js

Default port 80

Multimedia File Formats

Bitmap format	Characteristic
BMP	Large, original color, uncompressed
JPG/JPEG	Widely used. highly compressed, full-colour, lossy
GIF	Simple colour, transparent background, animation
PNG	small file size, millions of color, progressive rendering, opacity control

Vector format	Characteristic
SWF	Flash, animated
WMF	Windows based, cannot be edited extensively
EPS	Widely used
SVG	Used in web, based on XML

Audio format	Characteristic
WAV	Lossless, uncompressed
WMA	support streaming, popular
MP3	support streaming, widely used in portable players, popular
AAC	support streaming, Lossy compressed, popular

Video formats(All support streaming):

• WMV
• RM
• MOV
• MP4

HTML

Tag syntax

Tag: <tagName attribute1="value" attribute2="value"...>element in it</tagName>

Single tag: <tagName attribute1="value" attribute2="value"...>

HTML structure

• head: information about the web page
  
  • title: title of the webpage
  • meta: meta data of the webpage, such as encoding, and SEO related information etc.
• body: content of the webpage, after body tag
  
  • Attribute background: background image URL. (Default: repeat to fill the whole background)
  • H1, H2… H7: heading tag, for section headers, bold, usually larger font size.
  • p: paragraph, insert an empty line before and after the element in it.
    
    • Attribute align: alignment of the element in it. ‘left’(default), ‘right’, ‘center’
  • br: line break, single tag
  • pre: preformatted text, spaces and new line in it are preserved
  • center: center the element in it
  • hr: horizontal rule (line), divide web page into sections, single tag
  • Comment. Start: <!--, End: -->, string in it is ignored by the browser
  • Text formatting(format all text in it with specific style)
    
    • b: bold
    • i: italic
    • u: underline
    • sup: superscript(^superscript)
    • sub: subscript(_subscript)
    • Font: specify the font in it.
      
      • Attribute size: font size of the text
      • Attribute face: font family of the text
      • Attribute color: color of the text, RGB: #RRGGBB
  • list:
    
    • ol: ordered list. Elements in it are numbered
    • ul: unordered list, elements in it are not numbered (usually using dot)
    • li: list item, specify the item in ol and ul
  • table: define a table
    
    • tr: table row
      
      • th: table cell (for header)
      • td: table cell (for normal items)
        
        • Attribute colspan: number of columns that the cell occupy (horizontal, to the right)
        • Attribute rowspan: number of rows that the cell occupy (vertical, downward)
  • img: inline image, single tag
    
    • Attribute src: URL of the image
  • a: anchor tag. Two types:
    
    • <a href="URL">text/element</a>: link to another file
      If the URL is #xxxxx, where xxxxx is the name of another anchor in the page, it will link to that anchor (Scroll the page to that anchor).
    • <a name="xxx"></a>: define an anchor (Can use URL of #xxx in the same page to link to this anchor)
      Note that anchor can be linked from external webpage. For example, URL www.example.com#anchorName can link to an anchor named ‘anchorName’ in the page of ‘www.example.com’
• frameset: define a set of frames. No need to write body tag if using this tag
  
  • Attribute rows: define the rows in the frameset, seperated by commas. Each value represent the height of the row in pixel (Can use x% for percentage of the webpage). Can use * for the remaining spaces.
    Example: rows="123, *, 2*" means the first row is 123px high, the third row is the double of the height of the second row, while the second and the third row share the remaining height of the page (total height - 123px).
  • Attribute cols: define the columns in the frameset, seperated by commas. Each value represent the width of the column in pixel (Can use x% for percentage of the webpage). Can use * for the remaining spaces. Similar to rows.
  • frame: a frame, which shows another web page.
    
    • Attribute src: the URL of the shown web page

Algorithm

Concepts

• Need for systematic way of solving problems: increase the chance of finding the best solution
• divide and conquer: divide the problem into sub-problems and solve them one by one
• Problem solving procedures:
  
  1. Problem identification: know what the problem is
  2. Problem analysis: understand the whole problem, may use Input-process-output chart
  3. Algorithm design: design steps to generate a solution to solve the problem, may use pseudocode or flowchart
  4. Developing a solution: by top-down approach, divide the problem into sub-problems, solve them and join them together to form the solution to the overall problem. May use structure diagram. (dividing into sub-problems = stepwise refinement)
  5. Debugging and testing: check and ensure the program fulfills the purposes of it.
     
     • Syntax error: wrong syntax, occur when compiling the program
     • Run-time error: error when the program is running, use test data to check. Ex: division-by-zero
     • Logic error: caused by the logic design (wrong algorithm), usually expected result != actual result
  6. Documentation: describe in detail what is the program for and how it is designed, developed and tested
     
     • User manual: For common users, mostly how to use the program.
     • Program manual: For programmers, include problem definition, specification, algorithm, structure etc. So programmers can maintain the program in the future.
• User interface:
  
  • layout design (color, alignment, wording etc.)
  • consistency
  • different version for different type of users (efficiency vs easy to use)
  • fixed path of navigation to prevent the user from getting lost
  • appropriate placement of elements
  • form field validation
• Testing
  
  • Dry run (trace each line and each variable)
  • Test data
    
    • valid
      
      • normal
      • extreme (boundary of the valid input range)
      • boundary (critical value of the control structure)
      • each branch of the control structure (if, case)
      • each module
        
        • Invalid, to check if it can handle improper entries
• Compare
  
  • Time efficiency
  • Resource usage (memory)